Understanding Sys.Object

Sys.Object table contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure and so on.

select * from sys.objects

This will result all the object that is created within a database.

The’ type’ column in sys.objects table tell about object type for example ‘p’ indicate procedures, there is a ‘type_desc’ column that will describe the type.

select [name] from sys.objects where type = 'p'

This will result all the stored procedure that the user has created within a database.

This is the list of all possible values for this column (type):

• C = CHECK constraint
• D = Default or DEFAULT constraint
• F = FOREIGN KEY constraint
• L = Log
• P = Stored procedure
• PK = PRIMARY KEY constraint
• RF = Replication filter stored procedure
• S = System table
• TR = Trigger
• U = User table
• UQ = UNIQUE constraint
• V = View
• X = Extended stored procedure



We can also directly query sys.procedure table to get all the stored procedure that is created by user within a database.

select * from sys.procedures

Same way we can query sys.foreign_key to get all foreign key constraint created by user within a database.

select * from sys.foreign_keys

Sys.procedure

Understanding Sys.Object