A quick overview of Microsoft SQL Server Metadata
SQL Server has a set of tables that store information about all the objects, data types, constraints, … In SQL Sever 2008 these tables are called the system base tables. These tables can exist either only in the master database (and contain system-wide information) or in all the databases (and contain information about objects and resources of that database). These system base tables are not always visible by default. You won’t see these tables when you use the Object Explorer in SQL Server Management Studio and when you are not a system administrator you won’t see them either when executing the sp_help stored procedure. As an administrator one can retrieve these base tables by executing this query:
USE master; SELECT name FROM sys.objects WHERE type_desc = 'SYSTEM_TABLE';
But when you try to query one of the tables you’ll get an 208 error (‘Invalid object name’). The only way to see this data is using the DAC (Dedicated Administrator Console). It is important to note that these system base tables are used for internal purpose and there is no guarantee for compatibility.
In SQL Server 2008 there are three types of metadata objects. You have Compatibility Views, Catalog Views and Dynamics Memory Objects. The Dynamics Memory Objects don’t correspond to physical tables but contain data that is gathered from internal structures. We’ll explore these in a later post. The Catalog Views and Compatibility Views are actually views build on the system tables.
Compatibility Views
Prior to SQL Server 2005 you were allowed to see all the information in the system tables. Although this wasn’t intended many people used these system tables to develop their own reporting tools and applications to provide results that are not available using the supplied system procedures. You could assume that you would have to use the Dedicated Administrator Console to use these custom tools (due to the inaccessibility of the system base tables).
However you will be disappointed. In SQL Server 2008 many of the names and content has been changed so prior code will be completely useless, even within the DAC. The DAC is only intended in case you need emergency access and there is no support for any other use. To help you, SQL Server offers a set of compatibility views. These views allow you to have access to a subset of the SQL Server 2000 system tables. These compatibility views are accessible from any database.
Some of these compatibly views will have names that might be quite familiar to you (these include sysobjects, sysindexes, sysusers and sysdatabases) and some more exotic ones (sysmembers and sysmessages). Since it’s all about compatibility the views in SQL Server 2008 have the same names (and the same column names) as the SQL Server 2000 system tables. This means that your existing code won’t break.
It is however important to note that there is no guarantee that these views offer exactly the same results as the corresponding tables in SQL Server 2000. It’s also evident that these views won’t contain any information about new features in SQL 2008. These compatibility views should be used for backward compatibility only since they will be removed in a future version of SQL Server.
There are also compatibility views for the psuedotables of SQL Server 2000 (e.g. sysprocesses and syscacheobjects). These psuedotables are actually tables that are not based on data stored on disk. They are build from internal structures but can be queried exactly as if they were tables. Since SQL Server 2005 these are replaced by Dynamic Management Objects. It is important to note that there is no one-on-one correspondence between these psuedotables and the Dynamics Management Objects. For example: When you want to retrieve all information available in sysprocesses you have to access 3 Dynamic Management Objects: sys.dm_exec_connections, sys.dm_exec_sessions and sys_dm_exec_requests.
Catalog Views
Since SQL Server 2005 there is a set of catalog views as a general interface to the system metadata. All the catalog views (and the Dynamic Management Objects and compatibility views as well) are in the sys schema. You must reference this schema name when accessing these objects. Some of the names are quite similar to the SQL Server 2000 table names. Let’s give an example, there is a catalog view called objects in the sys schema, so when we want to reference the view, we execute this:
SELECT * FROM sys.objects;
However the names are similar, the columns in these catalog views can be very different from those in the compatibility views. This is (among others) the case with sys.indexes and sys.databases. You can observe this behavior by running these queries:
SELECT * FROM sys.databases; SELECT * FROM sysdatabases;
The compatibility view (sysdatabases) is located in the sys schema, so this view can be referenced as well as sys.sysdatabases. For compatibility reasons, the schema name is not required for these compatibility views. It is however required for the catalog views. (You can’t just select from a view called databases, you need to use the sys schema as prefix).
When we compare the output of the two above queries, we notice that the sys.databases catalog view has a lot more columns. Instead of a bitmap status field that needs to be decoded, each possible database property had its own column in sys.databases. When using SQL Server 2000, running the stored procedure sp_helpdb decodes all these database options, but because this is a stored procedure it is difficult to filter the results. As a view, sys.databases can be queried and filtered. A small example, when we want to know which of our database is in simple recovery mode, we can run this code:
SELECT name FROM sys.databases WHERE recovery_model_desc = 'SIMPLE';
The catalog views are built on an inheritance model, so that common attributes don’t have to be redefined. When we look at sys.objects we can see 12 columns (common to all types of objects). When we look at sys.tables we notice that we have the same 12 columns (in the same order) and an additional 15 columns with additional information that only applies to tables.
Some of the metadata can only be found in the master database and applied to system-wide data such as logins and databases. Other metadata appears in all databases and contains information such as the objects and the permissions.
Since the metadata objects are views, they are based on an underlying Transact-SQL (T-SQL) definition. It is possible to see the definition of these views using the object_definition function. When we want to see the definition of sys.tables, we execute the following:
SELECT object_definition (object_id('sys.tables'));
All the metadata that starts with sys_dm (such as sys_dm_exec_cached_plans) are Dynamic Management Objects. We’ll explore these in a later post. Although the catalog views are the preferred way to access metadata, there are some other tools available as well.
Information Schema Views
The Information Schema Views (introduced in SQL Server 7.0) were the original system table-independent views of SQL metadata. These views can be found in a schema called INFORMATION_SCHEMA. Some of the information that can be found in the catalog views can also be found in the information schema views. You should consider using the information schema views when you need to write a portable application that accesses the metadata.
However you should note that these views only show the objects that are with the SQL-92 standard. This means that there are no views for certain features (for example indexes, since these are not defined in the standard). Thus if your code doesn’t need to be portable or if you need metadata about features that are not standard (indexes, filegroups, the CLR, SQL Server Service Broker, …) it is suggested that you use the catalog views.
System Functions
Most of the SQL Server system functions are property functions. These property functions give us individual values for many of the SQL Server objects, the SQL Server databases and the SQL Server instance. The values returned by these property functions are scalar, thus they can be used as values returned by SELECT-statements and as values to populate columns in tables. The property functions available in SQL Server 2008 are:
- SERVERPROPERTY
- COLUMNPROPERTY
- DATABASEPROPERTY
- DATABASEPROPERTYEX
- INDEXPROPERTY
- INDEXKEY_PROPERTY
- OBJECTPROPERTY
- OBJECTPROPERTYEX
- SQL_VARIANT_PROPERTY
- FILEPROPERTY
- FILEGROUPPROPERTY
- TYPEPROPERTY
- CONNECTIONPROPERTY
- ASSEMBLYPROPERTY
Some of the information returned by these property functions can also be seen using the catalog views. The DATABASEPROPERTYEX function has a property called Recovery. This returns the recovery model of a database. To view the recovery model of a single database you can use the function like this:
SELECT DATABASEPROPERTYEX('msdb', 'Recovery');
The view the recovery model for all database you can use the sys.databases view:
SELECT name, recovery_model, recovery_model_desc FROM sys.databases;
In addition to these property functions there are also functions that serve as shortcuts to catalog views. When we want to find the database id for an ‘MyDatabase’ database it’s possible to either query the sys.databases catalog view or you could use the DB_ID() function. Both statements should return the same result.
SELECT database_id FROM sys.databases WHERE name = 'MyDatabase'; SELECT DB_ID('MyDatabase');
Note: When you see a column name ending with _desc you are looking at the ‘friendly named’ column. This column has always a paired column with a more compact (and cryptic) content. Both are available so that use can use what suits you best.
System Stored Procedures
The system stored procedures are the original metadata access tool of SQL Server in addition to the system base tabled themselves. Most of the initial system stored procedures are still available in the current versions however the catalog views are a bug improvement. Using the catalog views you can query the views as if they are tables. With the system stored procedures you have to accept the data in the way it’s returned. Some system stored procedures allow some limited parameters.
For the sp_helpdb stored procedure you can either pass no parameter to see all databases or use a parameter and see only the information for the specified database. However when you want to execute more complex queries you’re stuck. These complex queries are however easy to execute using the catalog views.