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.
End of Article
Prev. page
1
[2]
next page -->
