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.