MS SQL Server Interview Question:

How To List All Objects in a Given Schema?

Submitted by: Administrator

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 "ggl" and "dbo":

-- Login with 'sa'

USE GlobalGuideLineDatabase;
GO

-- What is "ggl"?
<pre>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 = 'ggl';
GO
name schema_id type_desc
------------------------------ ---------- ------------------
test 5 USER_TABLE
ggl_links 5 USER_TABLE
UQ__ggl_links__4222D4EF 5 UNIQUE_CONSTRAINT
DF__ggl_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</pre>
Submitted by: Administrator

<pre>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
ggl_links_indexed 1 USER_TABLE
DF__ggl_links__creat__403A8C 1 DEFAULT_CONSTRAINT
ggl_links_copy 1 USER_TABLE
ggl_links_dump 1 VIEW
ggl_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
...</pre>
Looks like we have a lots of different types objects stored in schema "dbo".
Submitted by: Administrator