What are Catalog Views in SQL Server ?

SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options.  One of the option is the Catalog Views.  These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data.


Catalog Views return information that is used by the SQL Server Database engine. Catalog views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.  All user-available catalog metadata is exposed through the catalog views.

Some catalog views inherit rows from other catalog views. For example, the sys.tables catalog view inherits from the sys.objects catalog view. The sys.objects catalog view is referred to as the base view, and the sys.tables view is called the derived view. The sys.tables catalog view returns the columns that are specific to tables and also all the columns that the sys.objects catalog view returns. The sys.objects catalog view returns rows for objects other than tables, such as stored procedures and views. After a table is created, the metadata for the table is returned in both views. Although the two catalog views return different levels of information about the table, there is only one entry in metadata for this table with one name and one object_id.

Script #1 - Example Catalog Views

--Display all the tables in the database SELECT * FROM sys.objects WHERE type_desc = 'USER_TABLE' --Display all the tables in the database --With additional columns specific to table SELECT * FROM sys.tables --Display all the views in the database SELECT * FROM sys.objects WHERE type_desc = 'VIEW' --Display all the views in the database --With additional columns specific to view SELECT * FROM sys.views

The catalog views have a hierarchical structure, on top of this hierarchy is sys.objects which contains meta information about all the schema scoped objects within a database.  Next in the hierarchy we have sys.tables, sys.views, sys.procedures, etc which contains (inherits) all the columns of sys.objects plus additional columns which relate to that particular type of object. For example if you see in SQL Server 2008, sys.objects has 12 columns whereas sys.tables has the same 12 inherited columns from sys.objects in the same order plus 15 more columns which are specific to the table object type.  Likewise sys.views has the same 12 inherited columns from sys.objects in same order plus more 7 columns which are specific to the view object type.