SQL Server 2012 System Stored Procedures
System stored procedures have been a favorite of SQL Server DBAs since the inception of SQL Server. They provide a rich set of info that covers many different aspects of SQL Server. They can return some of the same types of info as system views, but they generally return a fixed set of info that can’t be modified as you can when using a CHOOSE statement against the system views. That is not to say that they are not valuable; they are valuable, and they are particularly useful for people who have been using SQL Server for a long time. System stored procedures such as sp_who, sp_lock, and sp_help are tools for a DB professional that are as basic as a hammer is to a carpenter. System stored procedures have names that start with sp_, and they are found in the sys schema. They are global in scope, which allows you to execute them from any DB, without qualifying the stored procedure name. They also run in the context of the DB where you are working. In other words, if you execute sp_helpf ile in the AdventureWorks2012 DB, the DB files for the AdventureWorks2012 DB are returned. This same type of behavior exists for any stored procedure that is generated in the master DB with a name that starts with sp_. As an example, if you create a procedure named sp_helpme in the master DB and execute that procedure in the AdventureWorks2012 DB, SQL Server ultimately looks for and finds the procedure in the master DB.
System stored procedures are listed in the Object Explorer, in the Programmability node within Stored Procedures and then System Stored Procedures. There are far too many system stored procedures to list or discuss them all here. A quick check of the master DB lists more than 1,000 procedures. SQL Server Books Online offers in depth help on these procedures, which it groups into 18 different categories. You are likely to use only a handful of system stored procedures on a regular basis. What procedures you use depends on the type of work you do with SQL Server and your capacity to remember their names. below contains a sample set of system stored procedures that you can find useful.
| System Stored Procedure | Description |
|---|---|
| sp_configure | Displays or changes server-wide configuration settings. |
| sp_createstats | Creates statistics that are used by the Query Optimizer for all tables in a database. |
| sp_help | Provides details about the object that is passed to it. If a table name is passed to this procedure, it returns information on the columns, constraints, indexes, and more. |
| sp_helpdb | If no parameters are supplied, returns relevant database information (including the space used) for all the databases on an instance of SQL Server. |
| sp_helpfile | Lists the database files associated with the database you are connected to. |
| sp_lock | Displays current locking information for the entire SQL Server instance. |
| sp_spaceused | Provides the number of rows and disk space used by the table, indexed view, or queue passed to it. |
| sp_who | Lists current processes that are connected to an instance of SQL Server. |
Becoming familiar with some of the system stored procedures is well worth your while. Using them is a very fast and effective means for gathering info from SQL Server. They do not require the formation of a CHOOSE statement, and using them is often the easiest way to get info via a query window.