Sources for Metadata and Operational Info

SQL Server 2005 introduces a new set of system objects that let us see the internal workings of SQL Server in much more detail than before; dynamic management views (DMVs) and dynamic management functions (DMFs), as well as catalog views and compatibility views. We still have system tables (but this term now refers to much less than it used to), the information_schema views, and the metadata functions. Then there are DBCC commands . . . and the default trace . . .

Each of these different sources of information has its purpose even though there is sometimes a lot of crossover – it is possible to get some information from more than one source. I have provided a brief description of each source below, but how do you decide which one to use? From a safe programming perspective, the process might go something like this.

  1. If you can work with a scalar function and there is a suitable metadata function then use it.
  2. If you are after catalog information then use the catalog views, or if portable or standard code is a requirement, use the information_schema views.
  3. If you are after server or database state information then use the DMVs or DMFs.
  4. If you are writing or maintaining legacy code for SQL Server 2000 or earlier then you will have to use the compatibility views.
  5. If nothing above is suitable then there is probably only one option available for what you want, so use it.

Metadata Functions

Metadata functions return information on databases and database objects. Familiarity with these functions is worthwhile because they can reduce the need for writing code against system objects that are subject to change in future versions of SQL Server. Metadata functions are mostly scalar functions so are more suited to procedural code than complex queries. Examples of metadata functions are @@PROCID, DATABASEPROPERTYEX  and OBJECT_DEFINITION. The metadata functions are built-in functions so are not defined in any database. See the BOL entry Metadata Functions for a complete list.

Catalog Views

Catalog views are the recommended interface to persisted system metadata, all user-available catalog metadata is exposed through these views. More importantly, the future compatibility statement for catalog views says they may be 'extended' rather than 'changed' which provides for a little future proofing. All catalog views are in the sys schema and must be referenced with at least two-part naming. Some catalog views build on other catalog views, for example, sys.tables contains the 12 columns from sys.objects plus 12 additional columns, and for this reason if nothing else it's worth being familiar with what views are available – there's no point writing complex queries joining sys.objects to sys.objects numerous times if there's already a view that's done the work for you. The catalog views are defined in the MSSQLServerResource database and are predominantly queries against the system base tables. See the BOL entry Catalog Views for a list of the 16 categories into which the catalog views are grouped.

One point worth making because I've seen this written badly so many times is that object names alone do not uniquely identify an object, so the following code is risky;

SELECT object_id FROM sys.objects WHERE name = N'MyTable'

There could be more than one table with the name MyTable, or other objects with the same name. The above query would be better written as follows;

SELECT object_id FROM sys.objects o 
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id 
WHERE = N'MyTable'AND = N'dbo' and type = 'U'

Or even better, use a metadata function for this;

SELECT object_id(N'dbo.MyTable')

Information_Schema Views

The information_schema views are SQL Server's implementation of the SQL-92 standard metadata views. If you need to write portable or standard code then these views are your best bet. The terminology used by the views follows the SQL-92 standard rather than SQL Server, so a database is called a catalog, and not all SQL Server objects are included, for example, there is no trigger info. These views are defined in the MSSQLServerResource database and are all queries against the catalog views. See the BOL entry Information Schema Views for an overview of these views.

Dynamic Management Views and Functions

DMVs and DMFs return server state information. The kind of detailed information that is most useful when troubleshooting performance issues can be found through these objects. Unlike the catalog views, the future compatibility statement for DMVs and DMFs says they may be changed. All DMVs and DMFs are in the sys schema and are named with a dm_ prefix. The DMVs and DMFs are defined in the MSSQLServerResource database and most use a special (internal use only) version of the OpenRowset statement to produce their data. See the BOL entry Dynamic Management Views and Functions for a list of the 12 categories into which the DMVs and DMFs are grouped.

System Tables

The term 'system tables' in SQL Server 2005 refers to a series of tables storing information about Integration Services, Notification Services, Backup and Restore, SQL Server Agent, Log Shipping, Database Maintenance Plans, and Replication. The future compatibility statement suggests that the documented columns from these tables are safe to use but that the undocumented columns are not. The system tables are normal tables in the relevant database. See the BOL entry System Tables for an intro to these tables.

Note that the Database Maintenance Plans tables in the MSDB database are deprecated and not expected to be in the next version of SQL Server (although they are still there in the Feb 2008 CTP for SQL Server 2008).

Compatibility Views

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views in SQL Server 2005. These views are known as compatibility views and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available via system tables in SQL Server 2000. The compatibility views are defined in the MSSQLServerResource database. See the BOL entries on Compatibility Views and Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views for further information.

One point particularly worth noting about compatibility views is that some of them expose a risk of error around datatype overflows – they return SQL 2005 data in the corresponding SQL 2000 datatype, for example, a SQL Server 2005 user ID is an Int, whereas the equivalent SQL Server 2000 type is a smallint.

System Base Tables

The system base tables are those that were commonly referred to as the system tables in SQL Server versions prior to 2005. They are not really an option for monitoring or examining SQL Server configuration or performance because they are only directly accessible via a DAC connection. See the BOL entry System Base Tables for more info.

| Copyright 2007-2020 RMJCS