How To List All Objects in a Given Schema in SQL Server?

If you are wonder what objects are stored in a given schema as an object container, you can use view "sys.objects" to get a list of all objects in a schema. The tutorial exercise shows you how to list all objects in schema "fyi" and "dbo":

-- Login with 'sa'

USE FyiCenterData;
GO

-- What is "fyi"?
SELECT o.name, o.schema_id, o.type_desc 
   FROM sys.objects o, sys.schemas s
   WHERE o.schema_id = s.schema_id
   AND s.name = 'fyi';
GO
name                           schema_id  type_desc
------------------------------ ---------- ------------------
test                           5          USER_TABLE
fyi_links                      5          USER_TABLE
UQ__fyi_links__4222D4EF        5          UNIQUE_CONSTRAINT
DF__fyi_links__creat__4316F928 5          DEFAULT_CONSTRAINT

SELECT o.name, o.schema_id, o.type_desc 
   FROM sys.objects o, sys.schemas s
   WHERE o.schema_id = s.schema_id
   AND s.name = 'dbo';
GO
name                         s_id  type_desc
---------------------------- ---- --------------------------
Welcome                      1    SQL_SCALAR_FUNCTION
Age_In_Days                  1    SQL_SCALAR_FUNCTION
Top_Links                    1    SQL_INLINE_TABLE_VALUED...
Yearly_Stats                 1    SQL_TABLE_VALUED_FUNCTION
fyi_links_indexed            1    USER_TABLE
DF__fyi_links__creat__403A8C 1    DEFAULT_CONSTRAINT
fyi_links_copy               1    USER_TABLE
fyi_links_dump               1    VIEW
fyi_links_view               1    VIEW
Show                         1    SQL_STORED_PROCEDURE
Hello                        1    SQL_STORED_PROCEDURE
QueryNotificationErrorsQueue 1    SERVICE_QUEUE
EventNotificationErrorsQueue 1    SERVICE_QUEUE
ServiceBrokerQueue           1    SERVICE_QUEUE
DoubleX                      1    SQL_SCALAR_FUNCTION
...

Looks like we have a lots of different types objects stored in schema "dbo".

⇒ Default Schema of Your Login Session in SQL Server

⇐ Transferring Tables from One Schema to Another in SQL Server

⇑ Creating and Managing Schemas in SQL Server

⇑⇑ SQL Server Database Tutorials