Select query to get database objects

SELECT 
  [schema] = s.name, 
  [object] = o.name,
  o.type_desc
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
WHERE o.[type] IN ('P','U');

Some other answers you'll find on this or other sites might suggest some or all of the following:

• sysobjects - stay away, this is a backward compatibility view that has been deprecated, and shouldn't be used in any version > SQL Server 2000. See a thorough but not exhaustive replacement map here.

• built-in functions like OBJECT_NAME(), SCHEMA_NAME() and OBJECT_SCHEMA_NAME() - I've recommended these myself over the years, until I realized they are blocking functions and don't observe the transaction's isolation semantics. So if you want to grab this information under read uncommitted while there are underlying changes happening, you can't, and you'll have to wait. Which may be what you want to do, but not always.

• INFORMATION_SCHEMA - these views are there to satisfy the standards, but aren't complete, are warned to be inaccurate, and aren't updated to reflect new features (I blogged about several specific problems here). So for very basic information (or when you need to write cross-platform metadata code), they may be ok, but in almost all cases I suggest just always using a method you can trust instead of picking and choosing.