SQL SERVER - How to Find a Database Object

In the Previous Article, we have seen various Database Objects that are available in SQL Server. Now we will see how a particular Database object can be found.

Sometimes there will be some unusual cases like, to get the list of all primary keys in a database. If the number of tables is less, we can easily go through each and every table and list out the Primary keys, but if the count is too large, then it is a time consuming process.This type of cases will be easily handled by the system view called Sys.Objects.

Sys.Objects is a system view  which contains the Information about each and every the Database object with in the database.

Now we will see what this table contains :

Let us go to SQL Server Management Studio and execute the below query.

Select * From Sys.Objects

The result will be something like this as shown in the below screenshot, displaying all the available Database objects details like Name,Object_id,Parent_object_id, Type, type Description etc.

Among all those above columns of the Sys.Objects,  two columns have high significance in finding out a collection of specific database object. Those two columns are Type and Type Description.

Type and Type Description  specifies the type of database object that is available in the database.

Now Let us see all the distinct types of database objects in the database.

Select Distinct type,type_desc From Sys.Objects 

Now, As discussed earlier, In order to list out all the primary keys in this database, we will get all the records from Sys.Objects, filtering the records by the key type = ‘PK’.

Select * From Sys.Objects Where Type = 'PK'

The output is  :

Similarly, we can get a list of all specified database objects by replacing the appropriate Type in the above query.