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.
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.
|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.
|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.|
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 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.
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.
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.
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.
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).
|SQL Server Name||SQL-92 Name|
A user-defined datatype is called a domain in the ANSI SQL-92 standard.
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.
|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|