by Bill Wunder

With a few exceptions, the information used by SQL Server is stored system tables. Some relatively low level stuff such as storage allocation maps and memory distribution are managed by the kernel and are never materialized as SQL tables. The only visibility we DBAs have - if at all - to such data is through DBCC command and external tools. A few other kinds of information are similarly managed in the kernel, but for our convenience are manifest as a special class of view known as virtual tables. Virtual tables are never stored on disk but are available when ever the SQL Server Service is running. Processes, locks, and performance data - as master.dbo.sysprocesses and master.dbo.syslockinfo, and master.dbo.sysperfinfo - are examples of data available as virtual tables. For performance, manageability, and other proprietary justifications there is a small mount of data we cannot access though for the most part the actions of SQL Server are built around the same sort of data driven processing we use in our applications. As they like to say in Redmond: SQL Server eats its own dog food.

The majority of the system tables are stored much like the tables you and I create for our application. The database location of system tables is based on scope of function. System tables that have a critical server wide interest are primarily stored in the master database. Others that are used for SQL Agent, backup/restore, replication, log shipping, DTS, and maintenance are stored in the msdb database. SQL Server can run without the tables stored in the msdb database. SQL Server cannot run without the tables stored in the master database. In addition to the critical tables in master and the supporting tables in msdb, each database - including the system databases master, model, msdb, tempdb, and dist - have a structurally identical set of tables used to manage and support database objects, access, dependencies, and file groups. All system tables are owned by "dbo" in SQL Server 2000.

In SQL Server 2005 a change is coming. The schema for system tables will be "sys" - fact is the whole concept of schema being different than owner is coming. For example the table in each database dbo.sysobjects will become a view. Another view also comes along with the same purpose: sys.objects.  Through a backward compatibility feature we will still be able to access the data using dbo.sysobjects, but dbo.sysobjects will become a view rather than a table. Something tells me the details aren't quite all worked out here because in beta one there are actually a couple of duplicate names sys.objects. I'm going to keep watching for more details on system tables in SQL 2005, but in the mean time I'll just have to accept that there will be some changes.

From time to time you may have seen an offer to obtain a wall chart that shows the SQL Server 2000 system tables. If you can get your hands on one of these charts I highly recommend it. If not, there is an excellent - perhaps even better than the wall chart - alternative available for download from Microsoft: the SQL Server System Table Map. The is a help document. I suggest saving it on your workstation, adding a link to it from the Microsoft SQL Server program group, and referring to it often. Understanding the structure, data, and workings of the system tables is fundamental to the ability of a SQL Server DBA to support her database users.  Hopefully we will see a similar system map for SQL Server 2005 in the near future. One thing for certain is that it won't be the same as SQL Server 2000.

Of course, the first thing you will want to understand is that system table data should not be modified. Changing system data is no less risky than editing the registry or the boot.ini file. Good administrators know not to modify such data as rule, take all necessary precautions before doing so in the rare case that it is necessary, and don't confuse the high risk of changing data and the huge benefit of looking at the data in the day to day operation of a system. Good administrators also know to steer the application developers away from directly using system tables in applications. While any system table column that is documented in Books Online is technically fair game in application development, we have no control over how system tables might change in future releases. In fact, in SQL Server 2005 there is a major upheaval in the system architecture. Fortunately Microsoft is assuring that the changes are supported by a lot of backward compatibility smoke and mirrors. Nonetheless, moving forward it will be more difficult for developers - especially new to SQL Server developers - to make sense of the variety of system table access that is bound to evolve. Best practice for applications has been and will remain to use information schema views, system stored procedures, T-SQL statements and functions, DMO/SMO, and database application programming interfaces (API) catalog functions to access system table data.

On the off chance that you do need to modify a system table, there are a couple of important and easy rules to follow. The first is that you must be logged into the SQL server as a member of either the sysadmin or serveradmin server roles, and the second is that there is a very specific T-SQL sequence necessary to properly complete a change. Below is an example to help explain the sequence. The example is somewhat interesting if you find your self with the same login having different SIDs on different SQL Servers and you must frequently restore backups from one SQL Server instance to the other. If, as in this example, you reset the SID on one of the SQL Servers (the development box) to match the other (the production box) then you can eliminate the repetitive step of "fixing" the user/login mapping after each restore. Now you could drop the login and re-add it with the correct SID, but that would involve the loss of all permissions set for users mapped to that SID. Changing the login's SID in the master.dbo.sysxlogins table on one of the machines is a less disruptive approach. To make the change it is first necessary to enable updatability of the system tables in master with the sp_configure system stored procedure and then to allow that updatability state to change without stopping and starting the SQL Server service by issuing the RECONFIGURE statement. Notice that the RECONFIGURE cannot happen in the same batch as the actual update statement. Here I use dynamic SQL to execute the update in a new batch. Then once the system table is changed I turn off the updatability and once again issue the RECONFIGURE statement so that system tables are not inadvertently modified from this time.

Bill