Roll Your Own System Stored Procedures

If you're a SQL Server systems administrator (sa) who wants to expand your understanding of SQL Server and become a better administrator, you need an in-depth knowledge of SQL Server's system stored procedures. In "Beyond Point-and-Click Administration," October 2001, I discussed two aspects of SQL Server's system stored procedures. First, you must be able to interpret the T-SQL code that composes Microsoft-supplied system stored procedures. Second, you need to be able to write your own system stored procedures so that you can perform common tasks that the Microsoft-supplied routines don't accomplish. This column gives you some important caveats to keep in mind as you write your own system stored procedures.

In "Beyond Point-and-Click Administration," I explained that when you write a system stored procedure, you need to create it in the master database and give it a name that starts with the three characters sp_. Your procedure will then have the two special properties of system stored procedures.

First, your procedure will be accessible from any database without requiring the database name as a qualifier. Second, if your procedure accesses a system table that exists in all databases (e.g., sysobjects), SQL Server will access the table in the database that you called the procedure from. So if your procedure accesses the sysobjects table and you call the procedure from the Pubs database, you'll access the sysobjects table in Pubs; if you call the procedure from the Northwind database, you'll access the sysobjects table in Northwind. However, I didn't mention a few caveats involving duplicate procedure names and accessing the sysfiles system table.

Duplicate Stored Procedure Names

What happens when stored procedures have the same name in master and another database? For example, say you create in Pubs a procedure called sp_info, which is the same name as a procedure in master. Which procedure will execute when you call sp_info? Unfortunately, the answer is a moving target that depends on which SQL Server release and which service pack you're running.

In SQL Server 7.0 release to manufacturing (RTM), whenever you execute a procedure that begins with sp_, SQL Server first determines whether the procedure exists in master. If so, the procedure executes there. So if you call sp_info from the Pubs database, the procedure will still execute in master. In SQL Server 2000 RTM, some SQL Server 7.0 service packs, and SQL Server 6.5, the local procedure executes instead of a procedure in master that has the same name. Instead of trying to remember which rule applies to which release, simply make sure you don't use names for local procedures that are the same as names of procedures in master. In fact, to avoid confusing local procedures with special system stored procedures, I suggest that you never give a local procedure a name starting with sp_.

If you never duplicate system stored procedure names, you won't need to know another rule that governs which version of a procedure SQL Server uses, but I'll tell you about it anyway. The examples I've mentioned so far assume that you're writing new procedures such as the nonexistent sp_info that I mention above or the sp_object_list_by_owner procedure that Listing 1 shows and that I introduced last month. However, if you create in a user database a procedure with the same name as a Microsoft-supplied system stored procedure, your procedure will never execute—no matter what database you call it from. If you create a local version of sp_who or sp_lock, for example, SQL Server will ignore it and always execute SQL Server's version of sp_who or sp_lock in the master database.

Accessing the Sysfiles System Table

Another caveat regarding the special properties of system stored procedures involves the sysfiles system table. Sysfiles exists in every database and contains one row for every data and log file that belongs to a database. If you create a system stored procedure that accesses the sysfiles table, SQL Server will always read the data from the sysfiles table in the master database, not from the local database that you called the procedure from. I've never received a complete explanation from Microsoft for this special behavior; however, this table differs from most other system tables in that it isn't a real table but rather a pseudo table that takes up no storage space. SQL Server generates the contents of this table whenever you access it. You can verify that this table takes up no space, even though it seems to contain rows, by running the code that Listing 2 shows.

Although other pseudo tables (e.g., sysprocesses, syslockinfo) take up no space, sysfiles is one of the few such tables that exists in all databases. I used to think that characteristic has something to do with why system stored procedures find the sysfiles table only in the master database. However, the pseudo table sysprotects also exists in every database, and if you access sysprotects in your own system stored procedures, SQL Server will automatically use the sysprotects in the database from which the procedure is called. Sysfiles seems to be the only table that behaves differently.

You can write a system stored procedure that accesses the sysfiles table in any database, but before I tell you how, you might want to look at any system stored procedures you've already created. If you've read "Beyond Point-and-Click Administration" and created the sp_object_list_by_owner procedure that Listing 1 shows, use Enterprise Manager to look at the procedure in the folder for stored procedures in the master database.

Hundreds of procedures are listed in this folder, but they're in alphabetical order, so you can scroll down to find sp_object_list_by_owner. All the procedures that Microsoft supplies have the value System in the type column, but sp_object_list_by_owner is listed as a User procedure. Until I started investigating the special behavior of procedures that access the sysfiles table, I didn't think that the type value mattered. All these procedures, whether marked System or User, seemed to behave in the same special ways—specifically, these procedures can run from any database, even though they exist in master, and they will access system tables in the database from which they are called. But I noticed that procedures that access sysfiles behave differently, and I wondered whether the fact that Enterprise Manager listed some objects as System was significant. I then found and ran the sp_MS_marksystemobject procedure:

EXEC sp_MS_marksystemobject sp_object_list_by_owner

When I ran this procedure, passing my new sp_object_list_by_owner procedure as a parameter, Enterprise Manager showed the type value changed to System.

Only after I ran the sp_MS_marksystemobject procedure did SQL Server access the sysfiles table in the database that I called the procedure from. Listing 3 shows a simple example of a procedure that lists the logical and physical names of all the files in the current database's primary filegroup. I created this example in master and marked it as a system object so that you can successfully execute it from any database. Of course, if you want someone who's not an sa to be able to execute your new system stored procedures, you need to grant the appropriate permissions.

Finally, although you can verify that the sp_MS_marksystemobject procedure does what I said it does, you might be surprised to learn that the procedure isn't mentioned in SQL Server Books Online (BOL). In fact, the master database includes almost 500 procedures that start with sp_MS—none of them documented. Why is Microsoft keeping these stored procedures secret?

These sp_MS procedures aren't part of SQL Server; they help tools such as Enterprise Manager work properly. These procedures weren't written to be executed by you (as a SQL Server administrator) or by any other user who writes T-SQL code. The group of developers at Microsoft who wrote the tools is different from the group that wrote the SQL Server engine. The code that supports the tools is stored inside SQL Server, and you can see the code when you examine the existing objects; but because these procedures weren't intended to be executed directly, they don't need documentation. Look at the matter another way: Say you bought a tool from a third-party vendor, and the tool developers had written dozens or hundreds of procedures to do the work that the tool needed to do within SQL Server. Would you expect the tool developer to document the way the underlying procedures work? Tool developers document only the tool's functionality, not the way it works under the covers.

Although these procedures aren't documented, they do exist in SQL Server, and their definitions are available to us. In the coming months, as I continue to explore how to write system stored procedures, I'll examine more of these undocumented, but not invisible, procedures.