[What is sys.objects ? | How to Find user defined object in database? | How do I list user defined types in a SQL Server database? |Retrieve User Defined Object Details]
Dear Reader, I am sharing a very useful post for the developer as well as for the DBA. Some time before, DBA guys wanted to know the user defined object in the database by using the script.
Dear Reader, I am sharing a very useful post for the developer as well as for the DBA. Some time before, DBA guys wanted to know the user defined object in the database by using the script.
Now, Sql server provide sys.objects which can help retrieve user defined
object’s.
The sys.objects is a complete list of items that maintain a row for
each user-defined, schema-scoped object which is created within a database. By
querying sys.objects in database, you can get any user defined object details
Here is a simple script which retrieve all user defined object’s details .
SELECT name AS ObjectName, OBJECT_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS ParentObjectName, name, * FROM sys.objects
Here is one example of sys.objects
database usage. You can run below query to retrieve all the information about name
of the primary key, name of the table and the schema owner name of the table.
SELECT name AS ObjectName, OBJECT_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS ParentObjectName, name, * FROM sys.objects WHERE TYPE = 'PK'
You can use any of the following in your WHERE clause and retrieve
necessary information.
Object
type
|
Description
|
AF
|
Aggregate function
(CLR)
|
C
|
CHECK constraint
|
D
|
DEFAULT (constraint or
stand-alone)
|
F
|
FOREIGN KEY constraint
|
PK
|
PRIMARY KEY constraint
|
P
|
SQL stored procedure
|
PC
|
Assembly (CLR) stored
procedure
|
FN
|
SQL scalar function
|
FS
|
Assembly (CLR) scalar
function
|
FT
|
Assembly (CLR)
table-valued function
|
R
|
Rule (old-style,
stand-alone)
|
RF
|
Replication-filter-procedure
|
S
|
System base table
|
SN
|
Synonym
|
SQ
|
Service queue
|
TA
|
Assembly (CLR) DML
trigger
|
TR
|
SQL DML trigger
|
TF
|
SQL
table-valued-function
|
IF
|
SQL inline table-valued
function
|
U
|
Table (user-defined)
|
UQ
|
UNIQUE constraint
|
V
|
View
|
X
|
Extended stored procedure
|
IT
|
Internal table
|
No comments:
Post a Comment
Note: only a member of this blog may post a comment.