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, 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.
A New Procedure
Now, let's look at some tasks involving the system tables. You can't accomplish these tasks with any of the Microsoft-supplied methods I've discussed. SQL Server lets you define a column in a table as a computed column. The column definition is based on a computation and is recomputed each time a query accesses that column. For example, in the Pubs database, the titles table contains price and sales information for each book. If you want to store that information in a separate table, along with the gross sales for each book, you can create the table and populate it as Listing 3 shows. If you now select from priceinfo, you'll see that the gross column contains the product of the values in the price and sales columns. Executing sp_help on the priceinfo table will tell you that gross is a computed column, but no supplied way exists to see the definition of the computation.
The definition of the computed column is stored in the syscomments table. Syscomments has an ID column to show the ID of the object it's storing definition information for. The number column usually refers to a version of a stored procedure or trigger, but if the object is a table, the number column represents the ID of the column that has a definition. The syscolumns table contains the names and column IDs of all the columns and an id column, which specifies which table each column is part of. Finally, the syscolumns table has a column called IsComputed, with a value of 0 or 1 to identify whether the column is computed.
You can see all the computed columns in a database, along with their definitions, with the following code:
SELECT 'table' = object_name(cl.id),
'column name' = name, definition = text
FROM syscolumns cl JOIN syscomments cm
ON cl.id = cm.id
AND cm.number = cl.colid
WHERE iscomputed = 1
In addition, the output from this query shows that the syscolumns.IsComputed column is also a computed column. The definition shows that SQL Server computes the column's value by examining a bit in the syscolumns.colstat column:
(convert(int,sign((\[colstat\] & 4))))
This expression will return a 1 if colstat has the 4 bit on, and a 0 otherwise. You could turn the code that returns all the computed columns into a stored procedure and pass in values for table name and column name. The procedure would look something like the code in Listing 4. But this is just the beginning; to use this procedure in production, you'd need to add quite a bit of error checking.
Updating a System Table
At some point, you might want to update information in the system tables. One of my students wanted a way to change the logical name of a database file because she frequently makes multiple copies of databases. If you do a backup and restore or you use sp_detach_db followed by sp_attach_db, you can move the physical files and change their names, but the logical names stay the same. If you're completely changing the database name, it's consistent to have the name of the file change accordingly. SQL Server Books Online (BOL) doesn't explain how to do this, and the sysfiles table that stores the filenames isn't a real table, but a virtual one. (For information about virtual tables, see "System Tables," February 2000.) However, the undocumented table sysfiles1 is a real table; I've tested directly updating it to change the name column, which seems to work. The update looks something like this:
UPDATE sysfiles1 SET name = 'new name' WHERE name = 'old name'
The sp_helpfile procedure shows the new filename, as does stopping and starting SQL Server. Backing up and loading the database shows the new filename, unless you restore a backup you made before you updated sysfiles1. Also, I haven't yet tested this approach on a production server. Of course, direct updates to the system tables aren't supported, and you still need to enable such updates with the allow updates configuration option, but try it if you need new logical file names.
Unfortunately, information about system table contents is mostly undocumented, but you can learn about the system table contents by studying the text of the system procedures and by researching how the system procedures retrieve information for you.
