Querying Database Objects in SQL Server

The SQL Server contains a built-in schema called [sys] which can be used to query all the server objects created on the current database. This schema provides all the information used by each object of the database such as indexes, columns/fields, tables and views.

This is commonly used by other developer to actually developed an auto generator tool for their architecture.

For Microsoft documentation, please visit this link.

See below how to query the SQL Server Objects from the SQL Server.

Querying Objects

Inside the [sys] schema, if your SQL Server has intellisense, you can see all the schema definition objects if you put a dot after [sys] keyword. See below the screenshot.

You can select what schema object you may query for. We can use a default SELECT query to do a query.

Let say for example, we can query the tables, views, stored procedures and functions with the use of sys.objects catalog.

Querying Tables

We can use the sys.objects catalog to query the list of tables from the database. See sample code below.

SELECT object_id

      , name

      , type

      , type_desc

      , create_date

      , modify_date

FROM sys.objects

WHERE type = 'u'

ORDER BY name;

Type 'U' defined the object as the USER_TABLE. If we want to query the custom stored procedures, then we can filter the type 'P'.

Querying Fields

We can use the sys.columns catalog to query all the columns from the database. After querying the columns, we can use the object_id field to determine what table it is belong to. See sample below.

SELECT object_id

      , name

      , column_id

      , max_length

      , user_type_id

      , system_type_id

FROM sys.columns;

Joining the table and only querying the custom table columns.

SELECT o.object_id

      , o.name as tablename

      , c.name as columnname

      , c.column_id

      , c.max_length

      , c.user_type_id

      , c.system_type_id

FROM sys.columns c

INNER JOIN sys.objects o ON o.object_id = c.object_id

WHERE o.type = 'U'

ORDER BY o.name, c.name;

Querying Indexes

Same with the columns, we can use the sys.indexes to query the list of indexes under one table.  See our sample below.

SELECT i.object_id

      , o.name as tablename

      , i.name

      , i.index_id

      , i.type

      , i.type_desc

      , i.is_unique

      , i.is_primary_key

FROM sys.indexes i

INNER JOIN sys.objects o ON o.object_id = i.object_id

WHERE o.type = 'u'

ORDER BY o.name;

Querying the indexed columns by table can be filtered using the sys.index_columns. See below.

SELECT i.object_id

      , o.name as tablename

      , c.name as columnname

      , i.index_id

      , i.index_column_id

      , i.column_id

FROM sys.index_columns i

INNER JOIN sys.objects o ON o.object_id = i.object_id

INNER JOIN sys.columns c ON c.object_id = o.object_id AND c.column_id = i.column_id

WHERE o.type = 'u'

ORDER BY o.name;

Base in your requirements, you can expand and filter more specific objects inside [sys] schema.