System Tables
System tables contain
data about objects in the SQL Server databases (that is, metadata) as
well as information that SQL Server components use to do their job. Many
of the system tables are now hidden (in the resource
database) and are no longer available for direct access by end users.
In SQL Server 2008, compatibility views, have the same names as the system tables available in SQL
Server 2000. For example, if you had a query in SQL Server 2000 that
selected from syscolumns, this query continues to work in SQL Server 2008, but the results come from a view instead of a system table.
The system tables that you can view are now found in some of the system databases, such as msdb or master. You can use the Object Explorer in SSMS to view the system tables in these databases. Figure 1 shows the system tables listed for the master database in the Object Explorer.
The most significant number of viewable system tables is found in the msdb
system database. The system tables there support backup and restore,
log shipping, maintenance plans, Notification Services, the SQL Server
Agent, and more. You can retrieve a tremendous amount of information
from these system tables if you know what you are looking for. The following query selects from the system tables in msdb to report on recent restores for the AdventureWorks2008R2 database:
select destination_database_name 'database', h.restore_date, restore_type,
cast((backup_size/1024)/1024 as numeric(8,0)) 'backup_size MB',
f.physical_device_name
from msdb..restorehistory h (NOLOCK)
LEFT JOIN msdb..backupset b (NOLOCK)
ON h.backup_set_id = b.backup_set_id
LEFT JOIN msdb..backupmediafamily f (NOLOCK)
ON b.media_set_id = f.media_set_id
where h.restore_date > getdate() - 5
and UPPER(h.destination_database_name) = 'AdventureWorks2008R2'
order by UPPER(h.destination_database_name), h.restore_date desc
One of the challenges with
using system tables is determining the relationships between them. Some
vendors offer diagrams of these tables, and you can also determine the
relationships by reviewing the foreign keys on these tables and by
referring to SQL Server 2008 Books Online, which describes the use for
each column in the system table.
Caution
Microsoft does not
recommend querying system tables directly. It does not guarantee the
consistency of system tables across versions and warns that queries that
may have worked against system tables in past versions may no longer
work. Catalog views or information schema views should be used instead,
especially in production code.
Queries against system
tables are best used for ad hoc queries. The values in system tables
should never be updated, and an object’s structure should not be
altered, either. Making changes to the data or structure could cause
problems and cause SQL Server or one of its components to fail.
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 information that covers many
different aspects of SQL Server. They can return some of the same types
of information as system views, but they generally return a fixed set of
information that cannot be modified as you can when using a SELECT
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 database 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 database, without
qualifying the stored procedure name. They also run in the context of
the database where you are working. In other words, if you execute sp_helpfile in the AdventureWorks2008R2 database, the database files for the AdventureWorks2008R2 database are returned. This same type of behavior exists for any stored procedure that is created in the master database with a name that starts with sp_. For example, if you create a procedure named sp_helpme in the masterAdventureWorks2008R2 database, SQL Server ultimately looks for and finds the procedure in the master database. database and execute that procedure in the
Note
It is often useful to create your
own system stored procedures to make it easier to execute complex
queries against the system views (or to provide information not provided
by the built-in system procedures).
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
database lists more than 1,000 procedures. SQL Server Books Online
provides detailed help on these procedures, which it groups into 18
different categories.
Useful System Stored Procedures
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. Table 1 contains a sample set of system stored procedures that you may find useful.
Table 1. Useful System Stored Procedures
| 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. |
Many
of the administrative functions performed by SSMS can also be
accomplished with system stored procedures. Examples include procedures
that start with sp_add and sp_delete, which can be used to add and delete database objects. In addition, more than 90 system stored procedures start with sp_help, which return help information on database objects.
Tip
You can use the sys.all_objects
catalog view to search for available system stored procedures. This
catalog view lists objects that are schema scoped as well as system
objects. For example, the query SELECT * FROM sys.all_objects WHERE name LIKE 'sp_help%' returns all the system stored procedures that start with sp_help. You can turn to Books Online for detailed help on any of the system stored procedures. Just enter sp_ in the index search, and you see a list of them all.
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 information
from SQL Server. They do not require the formation of a SELECT statement, and using them is often the easiest way to get information via a query window.
