SQL Server system tables contain meta data, data about your data. Accessing this data can be important, but Microsoft strongly discourages accessing the system tables directly. So how can you get this information? My article "Accessing System Tables" (March 2000) discussed a group of system functions that can return meta data information and showed some stored procedures that can help. SQL Server 7.0 features two new methods of obtaining system meta data: property functions and ANSI schema views.
Property Functions
The property functions in SQL Server 7.0 can return even more information than the system functions in previous SQL Server releases. You use property functions differently from the way you use system functions, so they're classified as separate mechanisms. Most of the system functions I described last month require only one parameter, and you can invoke many system functions with an empty parameter list to specify that you want information about your current environment.
Most of the property functions take two parameters. The first parameter specifies the object you're interested in; the second specifies which property of that object you want to know about. Unlike with the system functions, you can't figure out the parameter values, at least not the second parameter's value, just by knowing your data. For example, a property function called ObjectProperty( ) takes an object_id as the first parameter and the name of a property as the second parameter. You can use ObjectProperty( ) to programmatically determine whether an object is a view or a table. SQL Server Books Online (BOL) tells you that two possible property values for ObjectProperty are IsTable and IsView. By using the IsView property from the Master database, you can determine that syslogins is a view:
SELECT ObjectProperty(object_id('syslogins'), 'IsView')
This function can contain nested functions, so you don't need to look up the object_id of syslogins as a separate operation. Most properties you can specify for the property functions are tri-valued—that is, they return one of three possible values. The value is 1 if the specified object has the designated property, 0 if the object doesn't have the property, and NULL if no answer can be given. A NULL might return if no such object exists or if you mistyped the property name.
Not all the properties return only three possible values. The property OwnerId, for example, can return any non-negative integer representing the user ID of the specified object's owner. Three properties—TableInsertTriggerCount, TableUpdateTriggerCount, and TableDeleteTriggerCount—can return the number of triggers of a particular type. To find out how many INSERT triggers are on the titles table, you can execute the following query:
USE pubs
SELECT ObjectProperty(object_id('titles'), 'TableInsertTriggerCount')
The ObjectProperty function has three other possible trigger properties: TriggerInsertOrder, TriggerUpdate-Order, and TriggerDeleteOrder. Their description in BOL says that the function returns the trigger type's firing order. But as a new feature of SQL Server 7.0, multiple triggers of the same type can exist on one table, so you can't control or predict what order they'll fire in. The Trigger*Order properties return an integer reflecting the trigger's creation order, but that value doesn't necessarily reflect the firing order. The description of these properties also erroneously says that the object type required for the first parameter is a table, but if you pass in the table ID instead of the trigger ID, SQL Server returns a NULL.
In February ("System Tables"), while discussing my research on why the Enterprise Manager displayed the dtproperties table as a system table, I showed you the query in Listing 1. SQL Server automatically generated this query when it rebuilt the table list during a refresh. The ObjectProperty function in this query examines several properties for each table. The code references the IsTable property to determine which objects to examine, so it doesn't need to analyze the columns or status fields in sysobjects. The tableIsFake property returns a 1 if the table is a pseudo-table, such as sysprocesses or syslocks, which materializes only when referenced. The IsSystemTable property returns a 1 if the table is a true system table, with an object ID of less than 100. A property called IsMsShipped is 1 if the SQL Server installation process created the table. Notice that all system tables have an IsMsShipped property value of 1, but not all tables with the IsMsShipped property value of 1 are system tables. These nonsystem tables with the IsMsShipped property include all the tables whose names start with spt instead of sys. You can mark an object so that it has the IsMsShipped property if you want it to show up as a system object in the Enterprise Manager. You use the stored procedure sp_MS_marksystemobject, which is undocumented but is in the Master database's list of system procedures. If you call this procedure and pass it an object name as a parameter, it changes one bit in the object's sysobjects.status column, resulting in the object's IsMsShipped property having a value of 1.
The documentation for the ObjectProperty function can show you dozens of other possible properties. I won't discuss each one in detail, but I encourage you to investigate this function on your own. Besides the ObjectProperty function, several other, similar functions are available in SQL Server 7.0. The DatabaseProperty( ) function gives you information such as which database options are enabled. The ColumnProperty( ) function takes three arguments and can tell you a column's properties, such as whether it has the identity property or allows nulls. File information is available through FileProperty( ) and FileGroupProperty( ), and you can retrieve full-text indexing information through FullTextCatalog-Property( ) and FullTextServiceProperty( ). As with Object-Property(), full documentation is available in BOL. Because Microsoft designed the functions to let you supply the property whose value you want SQL Server to return, the SQL Server development team can add new properties without having to create a new function.
Another View of Meta Data
The second new mechanism for accessing system tables in SQL Server 7.0 is information schema views, also called ANSI schema views, which Microsoft introduced into SQL Server as a move toward its goal of full ANSI compliance. If you execute sp_help in any database or use Enterprise Manager to look at the database views, you'll see a list of views representing SQL Server objects, with names in all uppercase. The names of these views include TABLES, VIEWS, COLUMNS, CHECK_CONSTRAINTS, and TABLE_CONSTRAINTS. If you want to see information about tables, you might try a simple SELECT query on the TABLES view:
SELECT * FROM TABLES
However, if you run that query, you get an error message saying that no such object exists. But how can that be, if you just saw the object? Inspecting sp_help's output will show you that the owner of these views isn't DBO, as with the other system objects, but a user named INFORMATION_SCHEMA.
The ANSI SQL standard doesn't support individual users as owners in a database; it uses a schema, which is a collection of objects (tables and views) that one user owns and the permissions on those objects. These schemas have names, which ANSI SQL specifies are to be used as the middle part of a three-part name. SQL Server uses the owner name in that middle position, so in SQL Server you can fully qualify an object name by using database_name.owner_name.object_name. If the object is in your current database, you can omit the database name. If the owner is you or DBO, you can omit the owner. But in this case, the owner is INFORMATION_SCHEMA, which appears as a row in the sysusers table. So, to get the information about tables from the TABLES information schema view, you need to use
SELECT * FROM INFORMATION_SCHEMA.TABLES
Now you'll get results. But say I want just the names of the user tables. I notice that the object names return in the column called table_name, so I run this query:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
Now I get not only the tables but also all the views, including the information schema views, which I don't want to see. I can see another column called table_type, which seems to have a value of either VIEW or BASE TABLE. So I rewrite my query once more:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'
This query gives me the information I want. But I could easily get the same result by accessing sysobjects directly:
SELECT name FROM sysobjects WHERE type = 'U'
Microsoft strongly recommends that you not access the system tables directly. But if I want to find the names of all my user tables as quickly and easily as possible, which of the two preceding queries will I probably type into Query Analyzer? (Hint: It's the one with the fewest keystrokes.)
You can combine property functions and information schema views to get information that's difficult to obtain in SQL Server 6.x: a list of all the databases that have the SELECT INTO/BULKCOPY option turned on. To get this information in SQL Server 6.x and earlier, not only must you access the sysdatabases table directly, you also need to do some bitwise arithmetic to check whether a particular bit in the sysdatabases status field is set to 1.
Determining which bit to check is another tricky problem. In SQL Server 7.0, you can execute the following query:
SELECT catalog_name FROM information_schema.schemata WHERE DatabaseProperty(catalog_name, 'IsBulkCopy') = 1
In most cases, you can get the same information by executing sp_helpdb for a list of all the databases on your server and examining the string in the status column to find out whether it lists SELECT INTO/BULKCOPY. However, this approach isn't as neat as the specific query, and it gives you more information than you might need. Also, if you have any databases that are inaccessible because of corruption or because they're set to single-user mode, sp_helpdb won't return any information for those databases. But you can use the DatabaseProperty function even if the database is inaccessible.
It's Your Choice
I've demonstrated four methods for extracting meta data from the system tables: system stored procedures, system functions, property functions, and information schema views. Microsoft guarantees that it will continue to support and document these methods fully, but this promise doesn't hold true for the system tables themselves. Any code you write that uses these tables might stop working with any upgrade or even a service-pack installation. I recommend not accessing system tables directly in stored application code if you can get what you need by using the supplied, supported alternative mechanisms. Next month, I'll show some examples of code for obtaining information that you can't get without directly accessing the system tables.
