Direct Access

In my past several articles, I've looked at the Microsoft-supplied tools—system stored procedures, system functions, INFORMATION_SCHEMA views, and property functions—for getting information from SQL Server system tables. You can access most of the information in the system tables by using one or more of these tools. But you still can't get all the information from the system tables that you might need, so sometimes you need to access the system tables directly. (However, the number of cases in which you need direct access to the system tables is much lower in SQL Server 7.0 than in earlier releases.)

New Tricks
As an example of when you might need to directly access the system tables, let's look at a functionality that Microsoft added to SQL Server 7.0. Earlier releases have no tools that you can use to easily tell whether triggers are attached to your tables. The capability to list triggers on a table is useful; if your applications behave in unexpected ways, you need to determine whether any triggers exist. But how can you do that if you're not using SQL Server 7.0? The Microsoft official courseware for SQL Server 6.5 erroneously stated that you can use the procedure sp_depends to see which triggers are on a table. But sp_depends shows you only which procedures or triggers reference a table in the body of the code, and in many cases, the trigger references the table it's based on only in the ON clause, not in the trigger body following the AS.

Microsoft added sp_helptrigger to the procedures supplied in SQL Server 7.0. But if you have an earlier release, you can use the sample solution in Listing 1, page 26, to create this procedure. Sysobjects stores the information about which triggers exist on a table. Three columns in sysobjects—deltrig, instrig, and updtrig—can contain a trigger's object ID. These columns have meaning only if the row in sysobjects represents a table or a trigger. A zero in the column means that the table has no trigger of that type. A nonzero value is the object ID of the relevant trigger; you can find its name by using the system function object_name(). If the row in sysobjects refers to a trigger, the deltrig column contains the ID of the table that the trigger belongs to, regardless of the trigger type, and the instrig and updtrig columns are unused. (SQL Server 7.0 added a new column called parent_obj to sysobjects. For triggers, the parent_obj column also contains the ID of the base table.)

Another popular operation that wasn't supported before SQL Server 7.0 is the ability to change an object's owner. Microsoft supplied sp_changeobjectowner with SQL Server 7.0. With earlier releases, the recommended solution to ownership problems created by employees leaving a company was to have the Database Owner (DBO) own everything. Of course, this solution doesn't help after object creation, so many SQL Server administrators have written procedures to change object owners. Remember that before creating a procedure to change the object owner, you need to set the configuration option to allow direct updates to system tables. After you set that option, you can change the owner by changing the value in the uid column of sysobjects. (Uid is the user ID of the object owner.) If your procedure was passed two parameters, @new_user_name and @object_name, the core of the code would look something like this:

UPDATE sysobjects
SET uid = user_id(@new_user_name)
WHERE id = object_id(@object_name)

As always, be extremely careful when modifying system tables. If someone is accessing a table when you change the owner, the results will be unpredictable.

Before I show you more code for writing procedures that access the system tables, let's look at how some existing system procedures work. The supplied system procedures are written in T- SQL code; you can examine this code in a couple of ways (if the procedure isn't an extended procedure). First, in the Master database, you can execute the sp_helptext procedure to obtain any procedure's definition. Also, in the \install subdirectory of your SQL Server installation directory is a text file called procsyst.sql, which contains the definitions of all the system procedures.

Mother of All Lookup Tables
Reading the system procedure definitions, you might notice frequent use of a table called spt_values. This table isn't a system table, but it comes with SQL Server. The spt_values table is just a big lookup table. Much of the information in the system tables is stored as codes, either in a field alone or as a bit in an integer field. A procedure that interprets these codes and translates them into meaningful English values produces its output by joining spt_values to the system table containing the coded value.

For example, the sp_dboption procedure reports on which database options are set in a database. The information is stored in three columns of the sysdatabases table: status, status2, and category. For example, the 4 bit in the status column means that the option SELECT INTO/BULKCOPY is set. The code for sp_dboption first needs to detect whether the 4 bit is set, then it finds the associated name for that option in spt_values. The procedure can't just check whether sysdatabases.status equals 4 because other bits might also be set. So you can use bit arithmetic. The bit operator for AND is an ampersand (&). The following condition will be true if the 4 bit is set, regardless of the values of the other bits:

sysdatabases.status & 4 <> 0

In spt_values, the type column identifies the type of information represented. No documentation is available, but you can figure out most of the type values by examining the table. For example, type C indicates a configuration value, L indicates the name for a locking mode, and DBR indicates a permission for a database role. For the information in sysdatabases, D indicates an option stored in the status column, D2 indicates a value stored in status2, and DC indicates a value stored in the category column, which shows the various replication options for the database. Listing 2 shows an excerpt from the sp_dboption procedure, which lists all the enabled options for a database.

The procedure sp_helpdb lists some options set for a database, but not all. If you check the T- SQL code for sp_helpdb, you'll see that it looks in only the status column, not in status2 or category. The procedure sp_dboption is the preferred way to see all the options that are set.