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 –Display all the tables in the database 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 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 –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
- Check out these related tips:
- Review my all previous tips.
