System Stored Procedures

A system stored procedure is a stored procedure with some special characteristics. These procedures, created when SQL Server is installed or upgraded, are generally used to administer SQL Server. They shield the DBA from accessing the system tables directly. Some are used to present information from the system tables, whereas others modify system tables. Information about login IDs, for instance, can be viewed with the sp_helplogins procedure and modified with sp_addlogin , sp_droplogin , and so on.

The earliest versions of SQL Server had no GUI-based administration tools, so a DBA had to have knowledge of the system stored procedures. With version 4.2 of SQL Server, Microsoft shipped two graphical administration tools, and SQL Enterprise Manager was introduced in version 6. The stored procedure is not an absolute must to administer SQL Server, but it is always a good idea to be familiar with the basic system stored procedures. Nearly 500 documented system stored procedures exist in SQL Server 2000, so it would be a tough job to learn the names and syntax for all of them. The total number of system stored procedures is about 995. Some of the undocumented stored procedures are called by other procedures, whereas others are called from SQL Enterprise Manager or other SQL Server tools and utility programs.

The following attributes characterize a system stored procedure:

• The stored procedure name begins with sp_ .

• The procedure is stored in the master database.

• The procedure is owned by dbo (that is, created by sa ).

These attributes make the procedure global. You can execute the procedure from any database without qualifying the database name. The procedure executes in the current database context regarding system tables.

If a stored procedure resides in the master database but does not begin with sp_ (for instance, xp_logininfo ), the procedure has to be fully qualified with the name of the master database when executed from a different database context, as shown in the following example:

 USE pubs  go exec xp_logininfo go Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'xp_logininfo'. EXEC master..xp_logininfo go account name           type   privilege mapped login name      permission path ---------------------- ------ --------- ---------------------- --------------- BUILTIN\Administrators group  admin     BUILTIN\Administrators NULL GOTHAM\SQLAdmin        user   admin     GOTHAM\SQLAdmin        NULL 

Although listed as a system procedure in Enterprise Manager and Query Analyzer, without sp_ at the beginning of the name, the xp_logininfo stored procedure is treated as a local stored procedure that resides in master, not as a system procedure. If your current database context is not the master database, you must fully qualify it with the master database name.

Although system stored procedures reside in master, they will run in any database context when fully qualified with a database name, regardless of your current database context. For instance, sp_helpfile shows information about the files configured for the current database. In the following example, when not qualified, sp_helpfile returns file information for the master database, and when qualified with pubs.. , it returns file information for the Pubs database:

 exec sp_helpfile  go name    fileid filename                   filegroup size     maxsize   growth ------- ------ -------------------------- --------- -------- --------- ------ master       1 e:\MSSQL\data\master.mdf   PRIMARY   14272 KB Unlimited 10% mastlog      2 e:\MSSQL\data\mastlog.ldf  NULL      3456 KB  Unlimited 10% exec pubs..sp_helpfile go name    fileid filename                   filegroup size     maxsize   growth ------- ------ -------------------------- --------- -------- --------- ------ pubs         1 e:\MSSQL\data\pubs.mdf     PRIMARY   9920 KB  Unlimited 10% pubs_log     2 e:\MSSQL\data\pubs_log.ldf NULL      11200 KB Unlimited 10% 

The context of system procedures is global only for references to the database system catalog tables. If a procedure refers to a user table, it will not be global even if a table with the same name and attributes exists in the current local database context.

In Listing 28.24, a global_example table is created both in the master and Pubs databases. A system stored procedure is also created in master that returns data from the global_example table. When you execute the sp_global_example procedure, you will see that it operates on the table in the master database only, regardless of the database from which it is executed. This is because the global_example table is not a system table.

Listing 28.24 System Stored Procedures, Which Are Global Only for System Tables, Not User Tables

 USE master GO CREATE TABLE global_example (a_string VARCHAR(50)) INSERT global_example  VALUES ('This is in the master database') GO USE pubs GO CREATE TABLE global_example (a_string VARCHAR(50)) INSERT global_example  VALUES ('This is in the pubs database') GO USE master GO CREATE PROC sp_global_example AS  SELECT * FROM global_example GO EXEC sp_global_example go a_string -------------------------------------------------- This is in the master database USE pubs GO EXEC sp_global_example go a_string -------------------------------------------------- This is in the master database 

Table 28.2 describes the eight categories of system stored procedures.

Table 28.2. System Stored Procedure Categories

Category	Description
System procedures	Used for general administration of SQL Server.
Security procedures	Used to manage login IDs, usernames, and so on.
Distributed queries procedures	Used to link remote servers and manage distributed queries.
Cursor procedures	Reports information about cursors .
Web Assistant procedures	Sets up and manages Web tasks (used by SQL Server Web Assistant).
Catalog procedures	Provides information about the system tables (used by ODBC).
SQL Server Agent procedures	Used by SQL Server Agent, provides access to the system tables in msdb. These are stored in msdb, so they are not global.
Replication procedures	Used to manage replication.

Some of the more useful system stored procedures are listed in Table 28.3.

Table 28.3. Continued

Procedure Name	Description
sp_who and sp_who2	Returns information about current connections to SQL Server.
sp_lock	Returns information about currently held locks.
sp_help [ object_name ]	Lists the objects in a database or returns information about a specified object.
sp_helpdb [ db_name ]	Returns a list of databases or information about a specified database.
sp_helptext [ object_name ]	Returns the CREATE statement for stored procedures, views, and so on.
sp_configure	Lists or changes configuration settings.

Rolling Your Own System Stored Procedures

SQL Server system administrators can create their own system procedures. All you need to do is create the procedure in the master database with the first three characters of the procedure name being sp_ . If you write your own system stored procedures, remember to grant execute permission in the master database on the system stored procedure to the appropriate users, groups, or roles that will need to be able to use the stored procedure.

TIP

If you want to create your own system stored procedures, one of the best places to start is to review the Microsoft-supplied system stored procedures and see how it does things. The source code for the system procedures is in the syscomments table in the master database and is not encrypted or hidden in any way. Looking at the system procedure source code will also help you learn more about the system catalog tables, learn what is stored in them and how it is stored, and learn how to retrieve the information in a meaningful way. I often " borrow " snippets of the system procedures when creating my own system procedures or queries that access the system catalog tables.

If the procedure needs to modify system tables, the allow updates configuration option must be set on when the procedure is created. If this option is on when the procedure is created, it remains in effect for the life of the stored procedure. The stored procedure will still be able to modify the system tables when it is executed, regardless of whether the option is on or off at the time of execution.

TIP

I often find that many of the Microsoft-supplied system procedures don't provide all of the information I want to see, or in a format that I want to see it. For example, with the increase in the size of object names from 30 characters to 128 characters, the output from sp_help no longer fits on one screen in Query Analyzer and is difficult to read. When scrolling over to see a column datatype, the column name is no longer visible on the screen. This is why one of the first things I do at a new installation is to create my own versions of sp_help , sp_helpindex , and sp_helpconstraint .

Copies of these procedures can be found on the CD under the filenames sp_SSU_help.sql , sp_SSU_helpindex.sql , and sp_SSU_helpconstraint.sql .

When creating your own system stored procedures, I recommend using a procedure name that will not conflict with any of the Microsoft-supplied system procedures. If you replace the Microsoft-supplied system procedures, you might break compatibility with existing applications. Also, if you install a Service Pack or run an upgrade, your versions of the system procedures might be replaced . I usually use my initials or the company's initials after sp_ to designate it as a custom system procedure. It also makes it easier to locate any custom system procedures when browsing the stored procedures in the master database.

CAUTION

Be careful if you implement custom system stored procedures that update the system catalog tables. It is easy to corrupt a database or the server if any of the records in the system catalogs are modified incorrectly.

A Caution on Querying System Tables Directly

Whenever possible, avoid embedding queries against the SQL Server system tables in your applications or stored procedures.

One of the areas that will involve the most work when porting an application to version 2000 is handling queries to the system tables (if any exist). The engineers at Microsoft have done a good job of providing a high level of backward compatibility with earlier versions of SQL Server. Nevertheless, some of the information in the system tables does not correspond between the versions.

A better approach than querying the system tables is to use the information schema views or system functions to retrieve metadata information.

System Information Schema Views

ANSI SQL-92 defined a set of views that provides information about system data. These views are available in SQL Server 2000. The advantage of using the views instead of querying the system tables directly is that the application is less dependent on the database management system or its particular version and potential changes to the system catalog tables.

Both SQL-92 and SQL Server use a three-part naming scheme for objects. Even though they use different names for each part, the names map quite nicely to each other (see Table 28.4).

Table 28.4. Three-Part Naming Scheme for SQL Server and SQL-92

SQL Server Name	SQL-92 Name
Database	Catalog
Owner	Schema
Object	Object

A user-defined datatype is called a domain in the ANSI SQL-92 standard.

SQL Server ANSI-Compliant Views

The information schema views are owned by the user INFORMATION_SCHEMA . The user is created by the installation script that creates the views. Queries against these views must qualify the object name with INFORMATION_SCHEMA , as in the following example:

 SELECT TABLE_NAME, TABLE_TYPE     FROM INFORMATION_SCHEMA.TABLES 

The information schema views display information applicable for the user who queries them (for instance, the tables that the user has permissions to use). Note that the names are in uppercase.

Table 28.5 lists the information schema views.

Table 28.5. Information Schema Views

Name	Description
CHECK_CONSTRAINTS	Information about check constraints
COLUMN_DOMAIN_USAGE	Information about which column uses user-defined datatypes
COLUMN_PRIVILEGES	Permissions at the column level
COLUMNS	Information about columns in the database
CONSTRAINT_COLUMN_USAGE	Table, column, and constraint name for each column that has a constraint defined
CONSTRAINT_TABLE_USAGE	Table, column, and constraint name for each table that has a constraint defined
DOMAIN_CONSTRAINTS	Information about user-defined datatypes that has rules bound to it
DOMAINS	One row for each user-defined datatype
KEY_COLUMN_USAGE	Information about which columns are defined as PRIMARY KEY or UNIQUE
REFERENTIAL_CONSTRAINTS	One row for each FOREIGN_KEY constraint
SCHEMATA	Returns character set information for each database (always the same in SQL Server)
TABLE_CONSTRAINTS	One row for each table constraint defined in the database
TABLE_PRIVILEGES	Permissions at the table level
TABLES	One row for each table in the database for which the user has permissions
VIEW_COLUMN_USAGE	Columns used in views
VIEW_TABLE_USAGE	Tables used in views
VIEWS	One row for each view in the database for which the user has permissions