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