The jury's still out about whether directly accessing SQL Server system tables is a good thing. The SQL Server 7.0 documentation states explicitly that you shouldn't access system tables, but the SQL Server 2000 documentation loosens up that advice a bit: Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query undocumented columns directly.
Those of you who read a lot of my writing know that I don't always follow this recommendation. Lots of undocumented columns contain useful information, and if I need to use that information for analysis or troubleshooting, I don't hesitate. Let's look at some of my favorite system tables, then I'll describe how I use information from one of them to troubleshoot system problems.
What about updating the system tables? The documentation stresses that "system tables should not be altered directly by any user." I completely agree with this advice. With older versions of SQL Server, I sometimes had to directly update a system table because they offered no other way to do what I needed. But in SQL Server 7.0 and 2000, Microsoft supplies enough system procedures and other tools to do any system-table modification I've required.
Although the documentation suggests that querying system tables is OK, your applications should use one of the following tools when possible to retrieve information that's stored in system tables:
- System stored procedures
- Information schema views
- System functions, including property functions
However, as much as I try to use the supplied tools to access system-table information, I use three system tables extensively when tuning and troubleshooting a system—syslockinfo, syscacheobjects, and sysprocesses. And none of these system tables is a real table at all, but rather they're pseudo tables. A pseudo table is one that doesn't take up any space. You might look at the number of rows to try to determine whether a table takes up any space, but that might not tell you for sure whether the table is a pseudo table. In the master database on my SQL Server, both sysreferences and sysfulltextcatalogs have 0 rows just because I haven't used those tables yet.
To determine whether a table is a pseudo table, you can look at the 11th bit (with a value of 1024) in the sysstat column of sysobjects. You can perform the bitwise AND operation sysobjects.sysstat & 1024, which in binary is all zeros except for the 11th bit. Because 0 AND anything is 0, all the bits except the 11th will be zeros in the output. If the 11th bit is also 0, the result will be 0, but if the 11th bit is 1, it will stay 1 in the output, and the result will be 1024. The following query returns the name of each table, the number of rows in the table, and the result of ANDing the table's sysstat value with 1024:
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)
Of course, the sysstat column in sysobjects is undocumented, so I shouldn't use it, no matter how much fun it is to twiddle with bits. Instead, you can use a system property function called OBJECTPROPERTY as follows to examine the property called TableIsFake:
SELECT Name = object_name(id)
FROM sysobjects
WHERE OBJECTPROPERTY(id , 'TableIsFake') =1
If the function returns a 1, the table is a pseudo table.
Prev. page
[1]
2
next page 
