SQL Server Metadata

Many times when you need to troubleshoot an SQL Server issue, you will need to collect metadata about the server, databases, and server resources in general. Here we will briefly review the mechanisms to collect these metadata.

System Base Tables

SQL server maintains a set of tables that store information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. These tables are called the system base tables.

• Some of these tables exist in master database –> contain system-wide information.
• Some exist in every database –> contain database specific information.

You can access these tables names only if you are logged in as a system administrator. You can access through:

• Run sp_help
• Run

  use master; 
  select name from sys.objects 
  where type_desc = 'SYSTEM_TABLE';
  

  
  

If you tried to select data from any of these system tables, you will got 208 error indicating that the object name is invalid. The only way to access these data is through dedicated administrator connection (DAC).

Keep in mind that these system base tables are used for internal purposes only within the database engine and are not intended for general use. They are subject to change and compatibility is not guaranteed.

Compatibility Views

Although it is possible to see data in the system tables in versions of SQL Server before 2005, it wasn’t recommended. For compatibility, SQL server 2005 and 2008 provided a set of compatibility views that allow access to a subset of the SQL server 2000 system tables. These views should be used for backward compatibility only; going forward, you should use catalog views.

Catalog Views

SQL Server 2005 introduced a set of catalog views as a general interface to the persisted system metadata. All the catalog views are in the sys schema, and you must reference the schema name when access the objects like:

select name from sys.databases;

For a complete list of catalog views categories, please consult http://msdn.microsoft.com/en-us/library/ms174365.aspx.

Information Schema Views

The information schema views comply with SQL-92 standard and all of it are in a schema called INFORMATION_SCHEMA. If you need to write a portable application that access the metadata you should use these views. This compliance come with the price of limited provided information (it provide the standard defined information only). If you need metadata about non-standard features, use catalog views.

For a complete list of Information schema views and its closest map to catalog views, please consult http://msdn.microsoft.com/en-us/library/ms186778.aspx

System Functions

Give us individual property values for many SQL Server instance, objects, databases. The values returned by system functions are scalar, so they can be used as values returned by SELECT statements like:

select DATABASEPROPERTYEX('msdb','Recovery');

For a complete list of system functions and its types, review http://msdn.microsoft.com/en-us/library/ms187786.aspx

System Stored Procedures

System Stored Procedures are the original metadata access tool but it had a drawback, basically you have to accept the data that it returns. Some of the procedures allow parameters but they are very limited. Catalog views are more enhanced and flexible in controlling what data appears.

I hope this brief post gives you an overall image about SQL Server metadata access mechanisms and which one to use according to your situation. 

SQL Server Dedicated Administrator Connection

Microsoft SQL Server provides a dedicated administrator connection (DAC) which allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted.

To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name. Object Explorer cannot connect using the DAC.

To connect to a server using the DAC

1. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

2. In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

3. Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

   The connection is made.

   If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

If you are trying to that and got the following error:

Dedicated administrator connections are not supported. (ObjectExplorer)

It means that you are trying to connect Object Explorer using the DAC. Object Explorer cannot connect using the DAC; only Query Window can. That means that you cannot press the New Query button; you have to use the Database Engine Query button.