"sys.objects" - Listing All Objects in a Given Schema in SQL Server

Q

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

✍: FYIcenter.com

A

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

2016-10-22, 1993🔥, 0💬