SQL Server Magazine

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, type
FROM sysindexes i JOIN sysobjects o ON o.id = i.id
WHERE 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.

   Prev. page   [1] 2     next page