Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

How To List All Objects in a Given Schema?

If you are wonder what objects are stored in a given schema as an object contrainer, 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".

(Continued on next topic...)

1. What Is a Schema in SQL Server 2005?
2. How To Create a New Schema in a Database?
3. How To List All Schemas in a Database?
4. How To Create a New Table in a Given Schema?
5. How To Transfer an Existing Table from One Schema to Another Schema?
6. How To List All Objects in a Given Schema?
7. What Is the Default Schema of Your Login Session?
8. Who Is the Owner of a Schema?
9. How To Change the Ownership of a Schema?
10. What Happens If You Are Trying to Access a Schema Not Owned by You?
11. How To Drop an Existing Schema?