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:
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
Some of the more useful system stored procedures are listed in Table 28.3.
Table 28.3. Continued
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.
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.
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
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