Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Understanding Catalog Views in SQL Server 2005 and 2008

MSSQLTips author Arshad Ali By:   |   Read Comments (4)   |   Related Tips: More > Dynamic Management Views and Functions
Problem

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.  The option I have selected in this tip 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. In this tip, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc.

Solution

SQL Server 2005 introduced the Catalog views as a more generic and standard interface to access the SQL Server system meta data. These views reside in sys schema, so whenever you have to use these views you will be required to provide schema name as two part name. The purpose of providing this catalog views instead of direct access to system tables is:

  • First, your code will not break if there are any structural changes in system tables.  This means the catalog views will continue to provide the same information (independent of any system base table implementation) irrespective of any changes in the system base table.
  • Second, these views provide descriptive column names which might be very helpful for understanding the data especially for the new users.

Based on these reasons, Microsoft recommends using the Catalog Views.  Here are a few examples:

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

Until SQL Server 2000, a member of the public database role was able to see system meta data for all the objects in the database irrespective of whether the user had explicit rights on the objects or not. Security has been enhanced in SQL Server 2005 and later versions to allow a user to view meta data of a securable if he is the owner or has been granted permission on it.  For example, if a user queries sys.tables, he will get a result set containing all the tables on which he has permissions.  If he does not have permission on any tables in the database, the query will return empty result set to him. For information visit this resource.

The Catalog views in SQL Server have been categorized into several categories depending on the meta data it provides.  Some of the examples of using catalog views are available here. There are no catalog views to for meta data stored in the msdb database, so to get information about replication, backup, database maintenance plan, SSIS, or SQL Server Agent catalog data you can use system tables which exist in the dbo schema.

How it has been organized...

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.

How it differs from Compatibility Views ...

SQL Server 2000 allowed us to query its system tables directly to get information about the SQL Server internal metadata. The problem with this was, any change in the structure of these system tables would break your code. To standardize this process, SQL Server 2005 introduced catalog views on top of these system tables and restricted the direct access to these system tables. This allows catalog views to provide the same information irrespective of any changes in the system tables, and hence no code break. But for backward compatibility SQL Server 2005 and 2008 provides compatibility views which have same name as previous system table name. This compatibility views are for backward compatibility only.  This means no additional compatibility views have been provided for the features introduced in SQL Server 2005 and later versions. The compatibility views are available in the sys schema, but providing the schema name is not mandatory as it is in case with the catalog views to maintain backward compatibility.

Script #2 - Compatibility views displaying user tables

--With Schema Name
SELECT * FROM sys.sysobjects 
WHERE xtype = 'U'
--Without schema name for backward compatibility 
SELECT * FROM sysobjects 
WHERE xtype = 'U'

How it differs from Information Schema Views ...

The Information Schemas are an ANSI/ISO standard for providing read only views which provide an interface for displaying the system meta data information related to tables, columns, views, procedures etc. All the information schema views in SQL Server has been provided in the INFORMATION_SCHEMA schema. The purpose of having information views is to have a standard (system independent) for portability across different relational databases. Information schema views provide very limited information about the meta data, for example it does not provide information about non standard or features specific to SQL Server i.e. CLR, Service Broker, etc. So if you do not intend to port your database to another ANSI compliant relational database, its recommended to use the standard Catalog Views which gives you more information specific to SQL Server.

Script #3 - Example Information Schema views

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.ROUTINES

 

Next Steps



Last Update: 2/3/2010


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, March 18, 2013 - 5:50:57 AM - Raj Read The Tip

Nice article on views..gud work,, thanks.


Monday, March 18, 2013 - 5:55:34 AM - Raj Read The Tip

so are dynamic management views part of catalog view, since they are under sys schema. and one more thing,I guess catalog view gives information specific to database under whose context they are queried , right ?

e.g use adventureworks

select * from sys.tables

will give all tables under this database only...


Wednesday, August 21, 2013 - 12:58:59 PM - FrenkyB Read The Tip

Good explanation on catalog views. It wasn't clear to me what is the difference between catalog views, compatibility views and information schemas. Very nicely and shortly explained, good job.


Wednesday, August 21, 2013 - 5:45:33 PM - elaykasa Read The Tip

A big fan!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.