When you execute the procedure sp_list_data, it will look for mytable in only the Master database. If no table of that name exists, you'll get an error message when you execute the procedure.
Server: Msg 208, Level 16, State 1,
Procedure sp_list_data, Line 2
Invalid object name 'mytable'.
In one situation, SQL Server special procedures can't find a system table in the current database: The sysfiles table, which has one row for every data and log file belonging to a database, exists in every database. If you create a stored procedure that starts with sp_ and references the sysfiles table, the procedure will always access the sysfiles table that exists in the Master database. The only explanation I've found (other than that it's a bug) is that sysfiles isn't a real table; it's a virtual table that doesn't have any space allocated to it. Sysfiles is the only virtual table that exists in every database. All the others, which I discussed last month, exist only in the Master database. Knowing this special fact about sysfiles might help you remember that this table behaves differently from other system tables.
System Functions
Besides the system stored procedures, another way to obtain meta data is through the use of system functions. Some system functions are shortcuts for accessing the system tables. For example, if you want to use the DBCC SHOWCONTIG command to check the fragmentation in the titles table, you need the table's object ID. You could query the sysobjects table to find that information:
SELECT id
FROM sysobjects
WHERE name = 'titles'
Or, you could use the object_id function:
SELECT object_id('titles')
To use the ID value in the DBCC SHOWCONTIG statement, you'd need to save the ID in an integer variable, then use that variable when calling DBCC SHOWCONTIG.
Table 1 shows a list of SQL Server 7.0 system functions. You can check SQL Server Books Online (BOL) to find out exactly how to use each function and to find out which ones are available in SQL Server 6.x. Most system functions are just shortcuts for retrievals from the system tables, but some go beyond shortcuts. For example, DB_ID, DB_NAME, and the user functions can give you information about the current connection. You can select the values of these functions without supplying any parameters:
SELECT db_name, suser_ name(), user_name()
This query returns your current database, your current login name, and your current user name. You can't get this information directly out of the system tables because there's no way to tell which row pertains to you.
The function INDEX_COL also is easier than directly accessing the sysindexes system table. This function tells you the column that an index key is based on. For example, to find out which column the first key in index 1 in the authors table is on, you could run this query:
SELECT INDEX_COL('authors', 1, 1)
Doing so tells you that the column is au_id.
That's Not All, Folks!
System stored procedures and system functions go a long way toward giving you access to information stored in the system tables. These methods are documented and supported, unlike directly accessing the tables in your SQL code. However, much of the information in the system tables can't be extracted using these two techniques. Next month, I'll look at two new mechanisms for extracting system table information: ANSI schema views and object property functions.