System Tables

As I've mentioned, SQL Server maintains a set of tables that store information about all the objects, datatypes, constraints, configuration options, and resources available to the SQL Server. This set of tables is sometimes called the system catalog. We've looked at a few such tables already, in particular sysconstraints and sysobjects. Some of the system tables exist only in the master database and contain systemwide information, and others exist in every database (including master) and contain information about the objects and resources belonging to that particular database.

The most common way of identifying a system table is by its name. All system tables start with the three characters sys, but that doesn't mean that everything that starts with sys is a system table. Another way of identifying system tables is by their object_id, which is always less than 100. A third way of identifying system tables is to look at the type column in sysobjects; system tables have a type of S. In SQL Server 2000, some of the objects that were system tables in previous versions have become views. You can get a list of these views by querying the sysobjects table directly, as follows:

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

If you run this query in the master database, you'll see six tables returned, and in all user databases you'll see only two. The views syslogins, sysremotelogins, sysopentapes, and sysoledbusers do not exist in the user databases.

Another type of system table isn't really a table at all, but a pseudo-table. These tables take no space and are not stored permanently on disk. Rather, SQL Server builds them dynamically every time you query them. In most cases, it doesn't make sense to store these tables on disk because their values change constantly and exist only while the server is running.

For example, storing syslockinfo on disk would be meaningless because locks do not exist unless SQL Server is running. In addition, lock information needs to be accessed so often and so quickly that storing it in a table would be much too slow. The information is actually stored in internal memory structures and is displayed as a table only when requested. You can select from syslockinfo as if it were a table, using the following statement:

 SELECT * FROM master..syslockinfo 

In addition, the stored procedure sp_lock retrieves information from syslockinfo, treating it just like any other system table. You can select from any of the tables that are really pseudo-tables, and most of them have one or more associated stored procedures for accessing their information.

Prior to SQL Server 2000, you could find out which tables were pseudo-tables by looking in the sysindexes table, which is where actual storage information is kept. There is a separate sysindexes table in every database, and every table and every index has a row in sysindexes. The rows in this table indicate where the data for the table or index is stored and how much space it takes up. Every table has a row in sysindexes with an indid (index ID) value of either 0 or 1. If the indid value is 0, the table has no clustered index and is called a heap. The value in the name column is the name of the table. If the indid value is 1, the table has a clustered index and the name of the clustered index is stored in the name column. The value in the id column is the ID of the table itself. Every nonclustered index on a table has a row in sysindexes with an indid value between 2 and 250. To see how much space the table is using, all you need to do is look at the value in the column called dpages in the row for the table. Any value greater than 0 means the table is actually taking some space.

The following query returns space usage information for all the tables with an object ID of less than 100, which generally means that it is a system table.

 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) 

A pseudo-system table is one that doesn't take any space. You might be tempted to look at the number of rows to determine whether a table takes up any space, but that might not give you the right answer. In the master database on my SQL Server, both sysreferences and sysfulltextcatalogs have 0 rows just because I haven't used those tables yet. In earlier versions of SQL Server, you could tell that a table was a real table by looking at the value in the dpages column. As soon as a table was created, SQL Server would allocate at least two pages for it, even without inserting any rows. In SQL Server 2000, as you saw earlier in this chapter, that just doesn't happen. The only way to tell whether a table is a pseudo-table is to look at the 11^th bit (with a value of 1024) in the sysstat column of sysobjects. You can perform a bit-wise AND operation of sysobjects.sysstat & 1024, which in binary would be all zeros except for the 11^th bit. Since 0 and anything is 0, all the bits except the 11^th will be zero in the output. If the 11^th bit is also zero, the result will be 0, but if the 11^th bit is 1, it will stay 1 in the output and the result will be 1024. The following query will show this:

 SELECT name = CONVERT(CHAR(30), o.name), rows, PseudoTable = o.sysstat & 1024, o.type FROM sysindexes i JOIN sysobjects o ON o.id = i.id WHERE o.id < 100 AND (indid = 0 OR indid = 1) 

The result rows that come back from the query that have a value of 1024 for the column labeled PseudoTable are the pseudotables. Here are the pseudotables tables in my master database:

 sysindexkeys sysforeignkeys sysmembers sysprotects sysperfinfo sysprocesses syslocks syscurconfigs syslockinfo syscursorrefs syscursors syscursorcolumns syscursortables syscacheobjects sysfiles 

In the pubs database, there are only five pseudotables:

 sysindexkeys sysforeignkeys sysmembers sysprotects sysfiles 

There's actually a much easier way to find out if a table is a pseudotable, without doing all this fancy bit arithmetic. SQL Server provides a whole suite of property functions, many of which I'll tell you about in relevant places throughout this book. The function OBJECTPROPERTY has a parameter TableIsFake. The first parameter of this function is an object ID, which you can get by using the OBJECT_ID function. The following statement returns the TableIsFake value for the syslockinfo and sysobjects tables in the master database:

 USE master SELECT OBJECTPROPERTY(OBJECT_ID('syslockinfo'), 'TableIsFake'), OBJECTPROPERTY(OBJECT_ID('sysobjects'), 'TableIsFake') RESULTS: ----------- ----------- 0 

The return value of 1 means that the table is a pseudotable, and the return value of 0 means that it is not. The function will return a NULL value if the table doesn't exist or if you misspell the name of the property.

Microsoft does not recommend that you directly query the system tables at all. The online documentation states:

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.

So, if you aren t supposed to directly access the system tables, how are you supposed to find out information about your system? SQL Server 2000 provides a number of tools for accomplishing this. We took a look in the preceding section at one of many possible property functions. There are also system stored procedures, such as sp_help and sp_helpconstraint. I ll tell you more about system procedures in Chapter 11. Finally, in compliance with the ANSI SQL Standard, there is a set of INFORMATION SCHEMA VIEWS, some of which we ll look at later in the book.