Rocking “Catalog Views” in SQL Server 2012

Hi All,
Today I came across an article on MSDN regarding the meta-data of SQL Server objects which led me to the “Catalog Views“.

What is it?
Catalog View” provides the information which is used by SQL Server Database Engine. So one thing is – “you can definitely trust this method“.

Moreover, Microsoft recommends this method for information retrieval because it is the most efficient way to obtain, transform, and present customized forms of this information. To make the information retrieval easier, Microsoft has categorized almost every object available in SQL Server in following catalog categories. (Pls see below).

You will also notice that some catalog views are using other ready-made views. Well, they are categorized in mainly 2 types: base views and derived views.

•  The base view contains a subset of columns and a super-set of rows.
•  The derived view contains a super-set of columns and a subset of rows.

Please find more on these 2 views and proper usage at MSDN here.

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

# Catalog View – Category
1 AlwaysOn Availability Group Catalog Views 
2 Change Tracking Catalog Views 
3 CLR Assembly Catalog Views 
4 Data Collector Views 
5 Data Spaces 
6 Database Mail Views 
7 Database Mirroring Catalog Views 
8 Databases and Files Catalog Views 
9 Endpoints Catalog Views 
10 Extended Events Catalog Views 
11 Extended Properties Catalog Views 
12 FileTable Catalog Views 
13 Full-Text Search and Semantic Search Catalog Views 
14 Linked Servers Catalog Views 
15 Messages (For Errors) Catalog Views 
16 Object Catalog Views 
17 Partition Function Catalog Views 
18 Policy-Based Management Views 
19 Resource Governor Catalog Views 
20 Scalar Types Catalog Views 
21 Schemas Catalog Views 
22 Security Catalog Views 
23 Server-wide Configuration Catalog Views 
24 Service Broker Catalog Views 
25 XML Schemas (XML Type System) Catalog Views 

I hope you will find this interesting just like me.
Thanks, Khilit

Rate this post


Leave a Reply