Property Functions, Schema Views

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, page 24. 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.