How To List All Objects in a Given Schema?
Submitted by: AdministratorIf 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
-- 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
---------------------------- ---- --------------------------
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
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Create a Testing Table with Test Data in MS SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a Large Table with Random Data for Index Testing in MS SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |