SALES: 1-800-867-1380
Microsoft Azure
Collapse the table of content
Expand the table of content
Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Catalog Views (Transact-SQL)

 

Updated: May 2, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they 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 catalog views.

System_CAPS_ICON_note.jpg Note


Catalog views do not contain information about replication, backup, database maintenance plan, or SQL Server Agent catalog data.

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. This can be summarized as follows:

  • The base view contains a subset of columns and a superset of rows.

  • The derived view contains a superset of columns and a subset of rows.

System_CAPS_ICON_important.jpg Important


In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM sys.catalog_view_name in production code because the number of columns returned might change and break your application.

The catalog views in SQL Server have been organized into the following categories:

AlwaysOn Availability Groups Catalog Views (Transact-SQL)Messages (for Errors) Catalog Views (Transact-SQL)
Azure SQL Database Catalog ViewsObject Catalog Views (Transact-SQL)
Change Tracking Catalog Views (Transact-SQL)Partition Function Catalog Views (Transact-SQL)
CLR Assembly Catalog Views (Transact-SQL)Policy-Based Management Views (Transact-SQL)
Data Collector Views (Transact-SQL)Resource Governor Catalog Views (Transact-SQL)
Data Spaces (Transact-SQL)Query Store Catalog Views (Transact-SQL)
Database Mail Views (Transact-SQL)Scalar Types Catalog Views (Transact-SQL)
Database Mirroring Witness Catalog Views (Transact-SQL)Schemas Catalog Views (Transact-SQL)
Databases and Files Catalog Views (Transact-SQL)Security Catalog Views (Transact-SQL)
Endpoints Catalog Views (Transact-SQL)Service Broker Catalog Views (Transact-SQL)
Extended Events Catalog Views (Transact-SQL)Server-wide Configuration Catalog Views (Transact-SQL)
Extended Properties Catalog Views (Transact-SQL)Spatial Data Catalog Views
External Operations Catalog Views (Transact-SQL)SQL Data Warehouse and Parallel Data Warehouse Catalog Views
Filestream and FileTable Catalog Views (Transact-SQL)Stretch Database Catalog Views (Transact-SQL)
Full-Text Search and Semantic Search Catalog Views (Transact-SQL)XML Schemas (XML Type System) Catalog Views (Transact-SQL)
Linked Servers Catalog Views (Transact-SQL)

Information Schema Views (Transact-SQL)
System Tables (Transact-SQL)
Querying the SQL Server System Catalog FAQ

Community Additions

Show:
© 2016 Microsoft