Inside SQL Server: System Tables

SQL Server maintains a set of tables that contain information about all the objects, data types, constraints, configuration options, and resources available to the SQL Server. This set of tables is sometimes called the system catalog. One subset of tables exists only in the Master database and contains systemwide information; another subset of tables exists in every database (including Master) and contains information about the objects and resources belonging to that database. This month, I give you details about these system tables, tell you how to identify them, and describe the kind of information you can find in them. Some of this information applies to SQL Server 6.x and 7.0, but SQL Server 7.0 requires some special considerations.

When Is a Table Not a Table?

The most common means of identifying a system table is by its name. All system tables start with the three characters sys, but not everything that starts with sys is a system table. Another way to identify system tables is by object ID; all system tables have an ID number less than 100. You can also identify system tables by looking at the type column in the sysobjects table; system tables have the type S. Some objects that were system tables in previous releases are views in SQL Server 7.0. You can get a list of these views by querying the sysobjects table directly:

     SELECT name FROM sysobjects     WHERE type = 'v'     AND name LIKE 'sys%'

If you run this query in the Master database, you'll see the names of six views, but only three in user databases. The views syslogins, sysremotelogins, and sysoledbusers don't exist in the user databases.

Another type of system table isn't really a table; it's a pseudo-table. Pseudo-tables take up no space and aren't stored permanently on disk. Instead, SQL Server builds them dynamically every time you query them. Storing these tables on disk usually doesn't make sense because their values represent volatile information within SQL Server that exists only while the server is running. For example, storing syslockinfo on disk would be meaningless because locks don't exist unless SQL Server is running. Also, lock information needs to be accessed so often and so quickly that storing it in a table would be too slow. SQL Server stores the information in internal memory structures and displays it as a table when you request it. You can use the following statement to select from syslockinfo as if it were a table:

SELECT * FROM master..syslockinfo

In addition, the stored procedure sp_lock retrieves information from syslockinfo just as other stored procedures interpret information in other system tables. You can select information from any of the pseudo-tables, and with most of them you can use one or more stored procedures to access the information the table contains.

You can find out which tables are pseudo-tables by looking in the sysindexes table, which holds storage information. Every database has a sysindexes table, and every table and index has a row in sysindexes. These rows tell where the data for the table or index is stored and how much space it takes up. The value in the id column is the table's ID. To see how much space the table is using, look at the value in the dpages column in the table's row. Any value greater than zero means the table is taking up some space. The following query returns space usage information for all the tables with an object ID number of less than 100—in other words, system tables:

SELECT name = convert(char(30),o.name), rows, dpages,o.id, typeFROM sysindexes i JOIN sysobjects o ON o.id = i.idWHERE o.id < 100 and (indid = 0 or indid =1)

As soon as you create a table, SQL Server creates an IAM page for that table. (See "The New Space Manage-ment," April 1999, for a discussion of IAM pages.) So tables with no rows and a value of 1 for dpages are real tables; they just don't have any rows yet. The previous query shows that the result rows that return a value of 0 for dpages are the pseudo-tables. Figure 1 lists the pseudo-system tables in my Master database.

A System Table Mystery

A few months ago, a student in my class noticed that when you select the tables for the Northwind database in the left pane of the Enterprise Manager, the data on the right shows a type column for each table. The student noticed that a table called dtproperties was listed as a system table, and wondered why.

Dtproperties contains information about all the database diagrams you create in Enterprise Manager, which seems like system information, but none of the usual system-table indicators are present. The name doesn't start with sys. The ID isn't under 100 (it's 389576426), and in sysobjects, its object type is U, which denotes a user table. So why does Enterprise Manager display this table as a system table?

I decided to play detective, using one of my favorite case-cracking tools, the SQL Server Profiler. I ran the predefined Sample 1 system trace to capture every batch coming into my SQL Server. Then in Enterprise Manager, I right-clicked the tables icon in the left pane and selected Refresh. I wanted to see what code Enterprise Manager would generate when it rebuilt the list of tables. I went back to the Profiler and saved the captured information to a SQL file. The query I captured, which SQL Server Enterprise Manager used to generate the table information, is in Listing 1.

This query has some interesting details, and among them I found an answer. Enterprise Manager's decision about whether to mark a table as a system table seems to be determined by the value that SQL Server returns in a column called SystemObj. The SQL Server Profiler query uses two property functions, which I'll talk about in a later column. An interesting point about this query is that when I run it through the Query Analyzer, all the other tables that Enterprise Manager shows as system tables have a value of 1 in the SystemObj column, but dtproperties doesn't.

I then created a new table with the name dtproperties in a different database. I created this simple table with no data in it, in the Pubs database:

CREATE TABLE dtproperties (col1 int)

When I looked at this table in Enterprise Manager, the right pane showed that the table type was System. Apparently, the Enterprise Manager is hard-coded to look for a table with this name. So, to expand the definition of system table: In addition to all the other indicators, if a table has the name dtproperties, according to the Enterprise Manager, it's a system table.

Microsoft recommends that you don't directly query the system tables. SQL Server Books Online (BOL) says: "The structure of the system catalog tables is dependent on the underlying architecture of SQL Server and changes from one version to another. Even an application that only issues SELECT statements may have to be at least partially rewritten when migrating to a new version of SQL Server if it directly queries system tables that change or are not present in the new version."

If, in an older release of SQL Server, you have stored procedures that access system tables, the upgrade wizard might not transfer them to SQL Server 7.0. In particular, if a SQL Server 6.x procedure selects information from a system table that no longer exists in SQL Server 7.0, the wizard won't upgrade that procedure. (Compare this situation to a procedure that references a user table that doesn't exist anymore. The wizard generates the procedure in SQL Server 7.0 and gives you a warning message.) Also, if you have a stored procedure that modifies data in any system table, the wizard won't upgrade it, even if the procedure would work fine in a SQL Server 7.0 environment.

So, if you aren't supposed to directly access the system tables, how can you find out information about your system? In future columns, I'll look at the recommended methods of accessing system information, including system stored procedures, ANSI schema views, and object property functions.

System tables in SQL Server contain the all-important meta data, the data about your data. This data includes information about table names, column names, and data types, so that SQL Server can properly process queries and return result sets. System tables contain information about valid users and their permissions, so data can be secure, and information about your SQL Server configuration, so you can predict and control the system's behavior. Understanding the information in these tables can help you understand why SQL Server behaves the way it does, and thus can help you build better solutions.

Learn more from "Querying the Sysindexes System Table."