Latest Post

Friday, 12 May 2017

How to Find user defined object in database

[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.  

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


Government Jobs