Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
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
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.