Querying SQL Server System Tables

Raise your hand if you're still writing queries and stored procedures (sps) that directly access SQL Server system tables. I know you're out there! Microsoft has been telling us for years that system tables can change between releases, so it's not safe to query them directly. Shame on you! It’s too bad because the tables are just so easy to get to and contain so much information. It's kind of like putting a big juicy steak on the floor, leaving your house for an hour, then getting mad when your dog gobbles it all up. How can we resist? System tables are just too darn tasty.

If we're not supposed to use system tables, how do we get system and catalogue information? Well-behaved SQL Server users grab their information from information schema views, system-stored procedures, Transact-SQL (T-SQL) statements and functions, SQL-Distributed Management Objects (DMO), and database API catalog functions.

According to SQL Server Books Online, "these components constitute a published API for obtaining system information from SQL Server. Microsoft maintains the compatibility of these components from release to release. The format of the system tables depends on the internal architecture of SQL Server and might change from release to release. Therefore, you might have to change applications that directly access the system tables before the applications can access a later version of SQL Server." It would be easier to resist temptation if all the information available from system tables was available from "components (that) constitute a published API for obtaining system information." Alas, this isn't always true. Here are two simple examples to give you an idea of the type of information you might be able to glean from unsupported use of system tables.

The open_tran column in the sysprocesses tells us the level of nested transactions for a given server process ID (SPID). This number is the same value you'd get by querying @@trancount directly from the connection. The open_tran column can be a great tool for a variety of specialized needs.

Here's another good example: The sysperfinfo table contains a Microsoft SQL Server representation of the internal performance counters that the Windows NT Performance Monitor can display. This information can be handy for custom monitoring applications and is especially valuable for a Windows 9x platform because Performance Monitor doesn't run on Win9x. In fact, SQL Server 7.0 contains 71 new system tables, so there are plenty of ways to be tempted. I'd love to have a fully documented, never-changing set of system catalogue information, but I'm not sure that will ever be 100 percent practical. After all, Microsoft needs the flexibility to change certain pieces of internal catalogue information from time to time. Microsoft could also enforce its don't touch rule by simply preventing access to system tables, but we'd hate that more. I guess I can live with the current status quo, taking my chances with surreptitious system table usage. As a responsible journalist, I'd never encourage you to directly access system tables because that's clearly unsupported. But I promise to wink and look the other way as you cherry pick all that juicy system table information.