SQL Server Magazine

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 1
Only 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 mytable
RETURN

   Prev. page   [1] 2     next page