| Downloads |
|---|
| 24190.zip |
In the past few T-SQL Admin columns, I've discussed system stored procedures and information schema views as the primary methods of obtaining metadata without directly accessing the system tables. I like these methods of obtaining metadata because the behavior of system stored procedures and information schema views isn't hidden. In other words, the definitions of the system stored procedures and information schema views are fully available as T-SQL text, and you can see which system tables you're accessing and how SQL Server extracts the system-table data. Also, Microsoft guarantees that it will clearly document any changes in the behavior of system stored procedures and information schema views. So although Microsoft recommends against accessing the system tables directly, you can use system stored procedures and information schema views to access information that the system tables contain.
However, SQL Server provides another supported method of accessing metadata—using property functions. The internal workings of property functions are completely hidden, so for someone who loves to know how things work "under the covers," this method is less exciting than using system stored procedures or information schema views. However, some information that you can obtain by using property functions isn't available any other way—not even by directly accessing the system tables—so knowing how to use property functions is a useful skill.
Microsoft introduced property functions in SQL Server 7.0 and expanded their functionality in SQL Server 2000. Table 1 lists SQL Server's property functions and shows which property functions are new to SQL Server 2000. The COLUMNPROPERTY(), INDEXPROPERTY(), and OBJECTPROPERTY() functions are enhanced in SQL Server 2000, and Microsoft has replaced SQL Server 7.0's DATABASEPROPERTY() function with DATABASEPROPERTYEX().
Almost all the property functions take two parameters. The first parameter specifies which object you're interested in, and the second parameter specifies which property of that object you want to know about. Figuring out the possible values of the parameters—especially the second parameter—is difficult. SQL Server Books Online (BOL) provides the information you need to determine the parameter values. For example, say the function OBJECTPROPERTY() takes an object_id as the first parameter and the name of a property as the second parameter. The BOL section about OBJECTPROPERTY() tells you that three of the many possible properties are IsTable, IsView, and IsIndexable. So, you can use OBJECTPROPERTY() to programmatically determine whether an object is a view or a table. And if the object is a view, you can see whether it's an indexable view. From the master database, you can use the IsView property to determine that syslogins is really a view:
SELECT OBJECTPROPERTY(object_id
('syslogins'), 'IsView')
Note that the OBJECTPROPERTY() function can contain nested functions, so you don't need to look up syslogins' object_id. Most specified properties for the property functions are tri-valued—that is, they return 1 if the specified object has the designated property, 0 if the object doesn't have the property, and NULL if no answer is available. The properties might return NULL, for example, if no such object exists (e.g., if you ran the above query from the Pubs database, where syslogins doesn't exist), if you mistyped the name of the property, or if the property doesn't apply to the given object. If you tried using the statement
SELECT OBJECTPROPERTY(object_id
('sp_who'), 'IsIndexable')
to see whether the sp_who procedure is indexable, you'd get a NULL result because indexing is meaningless for a stored procedure. Not all the properties can return only three possible values. The property OwnerId, for example, can return any non-negative integer representing the User ID (UID) of the specified object's owner. Three properties—TableInsertTriggerCount, TableUpdateTriggerCount, and TableDeleteTriggerCount—return a number representing 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 statement:
USE Pubs
SELECT ObjectProperty(object_id
('titles'), 'TableInsertTriggerCount')
Some property functions are particularly helpful for obtaining useful metadata. A property function that's new in SQL Server 2000 and apparently unknown even among some Microsoft Consulting Services consultants is SERVERPROPERTY(). This property function takes only one parameter, which is the name of a property you're interested in. The server that the function refers to is always the current server, and the properties available include the SQL Server edition (e.g., enterprise, standard), the SQL Server version number, the default collation, and the process identifier (PID). BOL contains the complete list of properties and syntax for this property function. One of the most useful properties for SERVERPROPERTY() is ProductLevel. Before the SERVERPROPERTY() property function was available, the only way to tell whether you were running the initial release of SQL Server or a service pack was to look at the last three digits of the cryptic string that the @@version() function returns. Then, you'd have to look at the README file for the installation to see what those three digits meant. In SQL Server 2000, you can simply run the following statement:
SELECT SERVERPROPERTY('ProductLevel')
A return value of RTM (which stands for release to manufacturing) means you're running the initial release, and the value SP1 means you're running Service Pack 1.
The PID value can be useful when you're running multiple instances of SQL Server on one machine because, when you're in the Windows Task Manager, using the PID value is the only way to tell which sqlservr.exe belongs to a particular instance. By using the Task Manager, you can compare the memory usage of each of the multiple instances, or if one of the instances hangs, you can use the process of elimination to determine which PID value to kill.
As I mentioned earlier, Microsoft replaced SQL Server 7.0's DATABASEPROPERTY() function with an enhanced version called DATABASEPROPERTYEX(). SQL Server 2000 supports the DATABASEPROPERTY() function for backward compatibility only. The DATABASEPROPERTYEX() function takes many possible parameters, including all the options that you set with the sp_dboption stored procedure in SQL Server 7.0 or by using the ALTER DATABASE keyword in SQL Server 2000. One shortcoming of both DATABASEPROPERTY() and DATABASEPROPERTYEX() is that you can see only one value at a time. An alternative is to use the sp_helpdb stored procedure, which lists all the options in one row (horizontally), as well as other information about one or more databases. Simply listing all the properties for a particular database isn't easy. The code in Web Listing 1 creates a stored procedure called sp_dbproperties, to which you need to pass the name of a database. (You can download this article's Web listing from the T-SQL Solutions Web site. Enter InstantDoc ID 24190 at http://www.tsqlsolutions.com and click Download the code.) The stored procedure returns the values of all the specified database's properties. A SQL Server support engineer from Microsoft wrote this basic code, and I've wrapped it inside a system stored procedure. So, for example, if you execute the command
EXEC sp_dbproperties Northwind
you get the results that Figure 1 shows.
Two of my favorite property functions are INDEXPROPERTY() and INDEXKEY_PROPERTY(). The INDEXPROPERTY() function uses the IsStatistics property to tell you whether a particular index on a table isn't really an index but is instead statistics. INDEXPROPERTY() uses the IsAutoStatistics property to tell you whether those statistics were created automatically or manually. Another INDEXPROPERTY() property, IndexDepth, tells you the number of index levels in an index B-Tree. Other INDEXPROPERTY() properties tell you whether the index is unique and whether it's clustered. The INDEXKEY_PROPERTY() function has only two properties. COLUMNID tells you what ordinal position an index's column is in and can be useful when you're dealing with composite index keys. ISDESCENDING tells you whether an index's key is internally sorted in ascending or descending order.
The details of all the property functions are too extensive to list. I recommend you spend some time perusing BOL to learn more about these useful functions. Even if you prefer to directly access system tables for some information, you'll find that property functions are indispensable for extracting information programmatically. I'd like to be able to dissect these functions and learn how they find requested information, but not knowing how they work internally won't stop me from using them when I need the information they provide.
