Beyond Point-and-Click Administration

Over the years, Enterprise Manager has evolved into a truly useful tool for SQL Server systems administrators (sas) and DBAs. Enterprise Manager lets you point and click to control security, manage database file sizes, perform and verify database backups, set up automatic tasks, and respond to error-message alerts. If you're a part-time administrator who manages a small database, Enterprise Manager might be all you need. However, if you need to do more than point and click, if you want to really understand what SQL Server does when you click an icon, or if you need more functionality than Enterprise Manager provides, you can dig in and write some code.

Related: T-SQL Best Practices, Part 1 and Part 2

The language that you use to modify any SQL Server version is T-SQL. This language is based on the ANSI SQL-92 standard specification but contains many additional features that exceed the standard. The name Transact-SQL originally applied to the Sybase dialect of the standard SQL language; Microsoft inherited the T-SQL name when Sybase and Microsoft jointly developed the language. After the two companies went their separate ways, both continued to use the T-SQL name. To be completely accurate, T-SQL Solutions should specify whether it's talking about the Microsoft or Sybase dialect. But this newsletter deals solely with Microsoft SQL Server programming, so you can assume the language under discussion within these pages is the Microsoft T-SQL dialect.

Setting Up the Foundation

Most of the information a DBA needs is metadata—information about data and databases. SQL Server includes a set of tables that maintains information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. This set of tables is sometimes called the system catalog. One set of tables exists only in the master database and contains systemwide information. Another set of tables exists in every database in your system—including the master database—and contains information about the objects and resources that belong to a particular database. Other databases contain system tables for special purposes: For example, the job and alert system uses msdb tables, and the replication system uses distribution database tables.

Microsoft recommends that you avoid directly accessing system tables and instead use other features that let you access the information you need. Because system table columns and the meaning of the data they contain might change in future versions, you should be cautious about including system table references in an application. However, as an administrator, you want to be able to use any information available, and system tables collectively form one of the richest existing sources of database information.

In future issues, the T-SQL Admin column will explore features such as system stored procedures, system functions, information schema views, and object property functions. The column will also examine situations in which none of these features let you retrieve the information you need, so you must go directly to the system tables. Whether you use the tools that Enterprise Manager provides or write SQL statements that directly access system tables, knowing what information these tables contain enhances your understanding of how SQL Server tracks your data.

Reinforce System Table Knowledge

A great way to learn about the information the system tables contain and how to use that information is to study the system stored procedures to see what tables and columns the procedures access. Microsoft wrote all SQL Server's system procedures in T-SQL code, and you can examine this code in a couple of ways (these methods don't work if the procedure is an extended procedure). First, in the master database, you can execute the sp_helptext procedure to get a report that shows the definition of a procedure. Second, look in your SQL Server installation directory; in the install subdirectory, you can find a text file called procsyst.sql, which contains the definitions of all the system procedures.

For example, SQL Server 2000 has a system table called syscomments that contains the definitions of all stored procedures, triggers, views, and user-defined functions (UDFs). The documented T-SQL technique for looking at these definitions is to use the sp_helptext procedure. By running sp_helptext to see the definition of sp_helptext, you can see that most of the procedure is just performing error checking. The following code sample shows the core of the sp_helptext procedure:

SELECT text FROM syscommentsWHERE id = OBJECT_ID(@objname) AND   encrypted = 0

The sp_helptext procedure uses a system function called OBJECT_ID(). The function's argument is the parameter (object name, or @objname) that the calling procedure passed to the sp_helptext procedure. The OBJECT_ID() function translates this object name into an object ID, which the function uses as a lookup value in the syscomments table. All the rows that have this particular ID value contain part of the requested object's definition. So, this SELECT statement returns one or more rows. The column called text in the syscomments table contains the definition's text. If the column called encrypted has a value of 1, the object is encrypted and the code can't return its definition. If encrypted has a value of 0, the object isn't encrypted and the code can return its definition.

So, looking at the system procedure sp_helptext can show you how SQL Server stores the definitions of programmable objects. Another procedure of about the same level of difficulty as sp_helptext is sp_who. To tackle a really tricky system procedure, look at sp_help or sp_helpindex. In future columns, I'll analyze some of the components of those procedures.

Microsoft's SQL Server documentation team produced a wall chart showing SQL Server 7.0 system tables, then updated the chart for SQL Server 2000. This poster shows the primary key and foreign key relationships between the system tables in the master database and the system tables in the user databases. Unfortunately, the SQL Server 7.0 poster wasn't widely available. Microsoft sent it to subscribers of the hard-copy Microsoft Developer Network (MSDN) newsletter, and at one point, SQL Server Magazine sent the poster to subscribers. But you can't order the poster independently.

Code Information Access

Becoming familiar with system tables can help you not only understand how SQL Server manages your data but also write code to access information in ways different from those that the system procedures provide. For example, suppose you want a list of all the objects a particular user owns. An existing procedure, called sp_help, returns all the objects that all users own, plus a list of all user-defined data types. By examining the text of sp_help, you can find the code that retrieves the objects; then, you can use that code to add a WHERE clause to limit the list to those that have a specific owner. You can also edit the SELECT list so that it doesn't return the owner name, because the name remains the same for all returned objects.

Listing 1 shows code from SQL Server 2000's sp_help that returns all the user objects. In this column, I don't explain all the details of this code; instead, I show that you can use this code without understanding it all. I could modify the code to add another WHERE clause to restrict the owner and omit the owner column from the SELECT list, as Listing 2 shows. Then, if I find that the code provides me with useful information, I might want to turn the code into a procedure and pass the username to the procedure as a parameter, as Listing 3 shows. Finally, I might decide that this procedure is useful enough to turn into a system stored procedure.

System procedures all start with the three characters sp_. You might be surprised to learn that the letters sp don't stand for stored procedure or system procedure; the letters sp stand for special. Procedures that have names starting with sp_ are special and behave in ways that no other procedures can. I recommend that you not use sp_ as a generic prefix for your user-defined stored procedures because confusing the user-defined procedures with the special ones that the system provides would be too easy.

Special procedures are special in two ways. First, most procedures are objects in one particular database. To execute a procedure in your current database, you must provide only the procedure's name. To execute a procedure in another database, you must prefix the procedure's name with the database's name and the owner's name. For example, if you're working in the Northwind database and you want to execute the reptq1 procedure in the Pubs database, use the following code:

EXECUTE pubs.dbo.reptq1

All the supplied system procedures exist in the master database, and the user DBO owns them. However, because of the sp_ prefix, you don't need to provide the database's or owner's name to execute them. If you want to execute the sp_help procedure, simply use the following code:

EXECUTE sp_help

The second reason that sp_ procedures are special is because even though the procedures exist in the master database, they reference system tables in the database that calls them. For example, the sp_help procedure returns a list of all the objects in the sysobjects system table. But because a sysobjects table exists in every database, you might not know which sysobjects table the sp_help procedure is using. The sp_help procedure references the sysobjects table from the database that is current when the code calls the procedure. If you call from Pubs, you get the object list from Pubs; if you call from Northwind, you get a list of Northwind's objects.

Although the prefix sp_ is special, it's not reserved. If you have permission to create procedures in the master database (which every sa has), you can create your own special procedures. In the master database, if you create a procedure that starts with sp_, your procedure will have the same special properties that the system-supplied procedures have. You won't have to prefix the name with master when you execute this procedure. In addition, the procedure can reference system tables in the database from which you executed the procedure. So, if I create a new procedure named sp_object_list_by_owner that lists objects by owner for any database, I can see a list of objects in whatever database I'm using—objects that any user owns in that database.

Understanding system tables and the T-SQL code that system stored procedures use lays the foundation for a wide range of topics. I'll discuss some of these topics in upcoming columns and leave others for you to discover on your own.