Sunday, February 10, 2013

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

      , as tablename

      , 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'

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
      , as tablename
      , 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'

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

SELECT i.object_id
      , as tablename
      , 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'

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

No comments:

Post a Comment

Place your comments and ideas