Inside SQL Server: Accessing System Tables

In last month's column ("System Tables"), I talked about what system tables are and how they're different from user tables. Microsoft discourages directly accessing system tables, but sometimes you need to access information in these tables. Here are a few more things that are special about system tables and some recommended methods for retrieving information from them.

The fact that you can't replicate system tables is well documented. If you use the SQL Server Enterprise Manager to set up replication, the system tables won't appear on the list of tables to choose from. If you try to set up replication manually, using the sp_addpublication and sp_addarticle stored procedures, and you try to base the article on a system table, you'll get this error message:

Server: Msg 14028, Level 16, State 1, Line 1Only user tables and procedures can be published.

Also, you can't create a trigger on a SQL Server 7.0 system table. However, the errors you might receive about it aren't informative. You'll get a message that you don't have permission to create the trigger, even if you're connected as sa. But it's not a permission issue; creating triggers on system tables simply isn't possible in SQL Server 7.0.

SQL Server 6.x doesn't generate an error message when you try to create a trigger on a system table. However, in most cases the trigger won't fire when you expect it to. Depending on how you define a trigger, it fires if you run an INSERT, UPDATE, or DELETE command, but most internal SQL Server actions that modify the system tables don't use these basic statements. For example, if you want a trigger to take some action every time a new table is created, you might create an INSERT trigger on sysobjects to add a row to an audit table, send an email message, or log an event. But SQL Server doesn't simply insert a row into sysobjects when a new table is created, so such a trigger would never fire. In some situations, triggers on SQL Server 6.x system tables could fire. For example, when you're adding a new login, SQL Server uses the stored procedure sp_addlogin. The text of this procedure shows that sp_addlogin performs an INSERT in the syslogins table to add a row for the new login name. So if you create an INSERT trigger on syslogins, that trigger will fire when a new login name is added.

Getting Information: System Stored Procedures

If Microsoft discourages you from accessing the system tables directly, how can you get system information? Four mechanisms are available in SQL Server 7.0: system stored procedures and system functions, which also exist in previous releases, and ANSI schema views and object property functions, which are new in SQL Server 7.0.

First, Microsoft has provided a large collection of system stored procedures with SQL Server. System procedures are available in all releases, but SQL Server 7.0 has the most. Some system stored procedures let you manually carry out most of the activities that the SQL Server Enterprise Manager offers. These procedures can set up replication, define tasks and alerts, define traces, etc. Most of these procedures either select from or modify one or more system tables. Some procedures, such as sp_help, retrieve basic system information; procedures such as sp_who and sp_lock help in troubleshooting system behavior.

All these procedures start with the characters sp_, but the letters 'sp' don't stand for stored procedure or system procedure--they stand for special. Procedures with names starting with sp_ are special; they behave in ways that no other procedures can. (I recommend that you not use sp_ as a generic prefix for user-defined stored procedures because such a naming scheme might lead to confusion between regular procedures and special ones.)

These sp_ procedures are special in two ways. To understand how they're special, be aware that all stored procedures are objects in a specific database. To execute a user-defined procedure in your current database, simply give its name. To execute a user-defined procedure in another database, you must prefix the procedure name with the database name. For example, if you're in the Northwind database and you want to execute a regular procedure in Pubs, you can use EXECUTE pubs.dbo.reptq1.

All the supplied system procedures exist in the Master database. However, because of their sp_ prefix, you don't need to give the name of the database when you want to execute them. If you want to run the sp_help procedure, just use EXECUTE sp_help.

The second special thing about the sp_ procedures has to do with the system tables. Although the procedures exist in the Master database, if you execute them without prefixing a database name, they'll reference system tables in the database they're called from. For example, the sp_help procedure returns a list of all objects in the sysobjects system table. But every database has a sysobjects table, so which sysobjects table does the procedure use? The sp_help procedure references the sysobjects table in whatever database you called the procedure from. If you called from Pubs, you'll get the list of objects from Pubs; if you're in Northwind, you'll get a list of Northwind's objects. In addition, if you prefix the call to these procedures with a database name, SQL Server responds as if you had called the procedure from the specified database. For example, if you use EXECUTE pubs.dbo.sp_help, you get the list of objects in Pubs, no matter what database you start out in.

The prefix sp_ is special, but it's not reserved. If you're a systems administrator, you have permission to create procedures in the Master database, so you can create your own special procedures. If you create a procedure in the Master database and the name of the procedure starts with sp_, your procedure will have the same special properties as the system-supplied procedures. For instance, you won't have to prefix the name with 'master' when you execute this procedure, even though it's an object in the Master database. And, if the procedure references a system table, it will refer to the table in the database you executed the procedure from.

The ability to refer to a table in the current database applies only to system tables. If you create a user table in the Master database, the situation changes. For example, suppose you created the following procedure in the Master database:

CREATE PROCEDURE sp_list_data AS   SELECT * FROM mytableRETURN

When you execute the procedure sp_list_data, it will look for mytable in only the Master database. If no table of that name exists, you'll get an error message when you execute the procedure.

Server: Msg 208, Level 16, State 1,Procedure sp_list_data, Line 2Invalid object name 'mytable'. 

In one situation, SQL Server special procedures can't find a system table in the current database: The sysfiles table, which has one row for every data and log file belonging to a database, exists in every database. If you create a stored procedure that starts with sp_ and references the sysfiles table, the procedure will always access the sysfiles table that exists in the Master database. The only explanation I've found (other than that it's a bug) is that sysfiles isn't a real table; it's a virtual table that doesn't have any space allocated to it. Sysfiles is the only virtual table that exists in every database. All the others, which I discussed last month, exist only in the Master database. Knowing this special fact about sysfiles might help you remember that this table behaves differently from other system tables.

System Functions

Besides the system stored procedures, another way to obtain meta data is through the use of system functions. Some system functions are shortcuts for accessing the system tables. For example, if you want to use the DBCC SHOWCONTIG command to check the fragmentation in the titles table, you need the table's object ID. You could query the sysobjects table to find that information:

SELECT id FROM sysobjectsWHERE name = 'titles'

Or, you could use the object_id function:

SELECT object_id('titles')

To use the ID value in the DBCC SHOWCONTIG statement, you'd need to save the ID in an integer variable, then use that variable when calling DBCC SHOWCONTIG.

Table 1 shows a list of SQL Server 7.0 system functions. You can check SQL Server Books Online (BOL) to find out exactly how to use each function and to find out which ones are available in SQL Server 6.x. Most system functions are just shortcuts for retrievals from the system tables, but some go beyond shortcuts. For example, DB_ID, DB_NAME, and the user functions can give you information about the current connection. You can select the values of these functions without supplying any parameters:

SELECT db_name, suser_ name(), user_name()

This query returns your current database, your current login name, and your current user name. You can't get this information directly out of the system tables because there's no way to tell which row pertains to you.

The function INDEX_COL also is easier than directly accessing the sysindexes system table. This function tells you the column that an index key is based on. For example, to find out which column the first key in index 1 in the authors table is on, you could run this query:

SELECT INDEX_COL('authors', 1, 1)

Doing so tells you that the column is au_id.

That's Not All, Folks!

System stored procedures and system functions go a long way toward giving you access to information stored in the system tables. These methods are documented and supported, unlike directly accessing the tables in your SQL code. However, much of the information in the system tables can't be extracted using these two techniques. Next month, I'll look at two new mechanisms for extracting system table information: ANSI schema views and object property functions.