Investigating System Objects

What's a system object? Is it just an object marked as system in Enterprise Manager? In "A Bit About Bits," December 2001, I mentioned that you can mark any object you want as a system object by passing the object name as a parameter to sp_MS_marksystemobject. After you mark the object, Enterprise Manager displays the object as type system.

But making an object such as a stored procedure a system stored procedure means more than just displaying the procedure as a system type. The stored procedure also needs to be accessible from any database, which means it needs to have the sp_ prefix and needs to be in the master database.

In addition to the system stored procedures in the master database, the msdb database contains a set of procedures that start with the sp_ prefix. Are these system objects? Enterprise Manager displays their type as system, but most of these procedures have no other special properties. Thus, they aren't system procedures. For example, you can't access these procedures from another database without qualifying the procedure name with the database name. One of these msdb procedures is sp_add_operator, which adds operators for use with alerts and jobs and takes as many as 13 parameters. (One parameter is required; the other 12 are optional.) If I execute the following query from the msdb database:

EXEC sp_add_operator

I get an error message that says a parameter is missing. If I execute this procedure from any other database, I get an error message that says the procedure can't be found. If this procedure were in the master database, I'd be able to access it from any other database without getting the error message.

All these msdb sp_ procedures have the value 1 (true) for OBJECTPROPERTY IsMSShipped. In "A Bit About Bits," I explained that turning on the bits that the sp_MS_marksystemobject procedure sets is the same as giving the object the IsMSShipped property because when you run the procedure on an object, the object's IsMSShipped property becomes true. But when I examined these msdb procedures more closely, I realized that these two actions aren't always equal. All the sp_ procedures in msdb have the property IsMSShipped. Because of that property, Enterprise Manager will show the object's type as system. However, not all these procedures have the two bits set that sp_MS_marksystemobject turns on. So how can you determine which procedures have those bits set?

In "A Bit About Bits," I showed you how to turn on one or more bits in an integer column by applying the bitwise OR (|) operator to the column and to a constant composed almost entirely of 0s except for 1s in the 31st and 32nd bit positions. The following is the UPDATE statement from the sp_MS_marksystemobject procedure:

UPDATE sysobjectsSET status = status | 0xC0000000WHERE  id = object_id(@objname, 'local')

Applying the OR operator to the operands status and 0xC0000000 turns on the bits corresponding to 0xC0000000, which are the two highest bits in a 4-byte integer. To determine whether a set of bits in an integer field contains all 1s, use the bitwise AND (&) operator, which returns a result of 0 unless both bit operators are 1. If you apply AND to the status field and to 0xC0000000, all the bits but the two highest result in 0. The two highest bits result in 1 only if they're 1s in the status field. If the two highest bits are 1s in the status field (i.e., the bits marking a system object are set), the result of applying AND to that status field and to 0xC0000000 will be the value 0xC0000000.

Listing 1 shows a code example that returns all the procedures in the msdb database that start with sp_. These procedures all have the value 1 for the property IsMSShipped. However, the procedures don't all have the two highest bits of the status field set to 1. For example, the query that Listing 1 shows returned 174 procedure names on my server. But when I added the AND clause to the WHERE condition to check for the two highest-order bits being set to 1, as Listing 2 shows, I got only 14 object names. On further investigation, I discovered that if only bit 32 in the status column was set to 1, a procedure would appear to have the property IsMSShipped. All the sp_ procedures in msdb have bit 32 set to 1; only a few of the procedures have both bits 31 and 32 set to 1.

If you compare the AND operations in the example that Listing 2 shows with the example from the sp_helpindex procedure that Listing 1 in "A Bit About Bits" shows, you'll notice a slightly different method of determining whether bits are set to 1. The method in the sp_helpindex procedure works only for testing one bit. Because sp_MS_marksystemobject sets two bits to the value 1, I needed to use a slightly different method.

So, the sp_ procedures in the msdb database have different sets of features that sometimes define a system object. The procedures all have the special prefix sp_, and they all show up as the type system in Enterprise Manager. But they aren't accessible from any database, and only some of them have the special bits set that define a system object. So, are these procedures system objects? The answer depends on whose definition you use. By my definition, they're not. Let's take a closer look at two other types of system objects: system functions and information schema views.

System Functions

SQL Server 2000 provides system functions that fall into several categories. Some system functions have been available since the earliest releases of SQL Server and are similar to built-in operators. Functions such as Substring(), GetDate(), and DB_Name() aren't written in T-SQL and don't show up as objects in the sysobjects table. In SQL Server 7.0, Microsoft introduced a set of built-in functions for working with full-text data that you use in the FROM clause of a query and that return a rowset rather than a scalar value. These built-in functions—e.g., OPENQUERY(), OPENROWSET()—also aren't written in T-SQL and aren't objects in the sysobjects table. Still, you could consider all these functions (which Microsoft provides as part of the SQL Server product) as system functions because they're predefined and they're available from any database.

SQL Server 2000 lets you write your own functions. Any functions that you create by using the CREATE FUNCTION command are in T-SQL and are stored as objects in the sysobjects table. You can create scalar functions that return one value or table-valued functions that return a rowset. In addition, Microsoft supplied a set of new table-valued functions that have several special properties. You can see a list of these Microsoft-supplied functions, which Figure 1 shows, by using the Index tab in SQL Server Books Online (BOL) and looking for entries that start with fn_. BOL describes what each function does.

Unlike the sp_ prefix for system stored procedures, the prefix fn_ has no special meaning. You might want to use fn_ to show that your object is a function, or you might use a different naming convention. However, SQL Server 2000's seven built-in functions have some special features.

First, SQL Server stores the definitions of these functions only in the master database, but you can access the functions from any database. Second, you must use a special convention to call these functions. The first time I saw the following syntax, I thought someone had made a typing error. But the syntax is legitimate. You need to preface the function name with a double colon, as the example below shows:

SELECT * FROM ::fn_helpcollations()

Third, the owner of these system functions is a special user called system_function_schema. To see the name system_function_schema, list the master database's objects in Enterprise Manager or execute sp_help in the master database.

Although Microsoft asserts that you can't write your own system functions and make them available from any database, you can—but the process isn't easy or supported. You need to be aware that system functions that you write in one SQL Server release might not work in a subsequent release because they won't be supported. Let's look at a technique that SQL Server instructor Itzik Ben-Gan came up with for writing a system function that's accessible from any database. Such a system function must be created in the master database, start with fn_, be owned by the user system_function_schema, and use only lowercase letters in its name.

Usually, if you try to create an object that the user system_function_schema owns, you receive an error message stating that no such user exists, even though that value is listed as a name in the sysusers table. To create objects that system_function_schema owns, you must enable updates to the system tables, which is why this technique is unsupported and undocumented. In addition, you must be in the system administrator role to allow updates to system tables.

Listing 3 shows an example of a system function (taken from my book Inside Microsoft SQL Server 2000, Microsoft Press, 2000) that computes the factorial of any integer less than 34. When you look at this example, you need to note one more restriction on system table-valued functions: You can't create a recursive system function. So, to create a factorial function as a system function, you have to use an iterative approach. To invoke the function that Listing 3 shows, you can use it in a SELECT list:

USE  pubsGOSELECT fn_factorial(5)GO

I don't have space in this column to provide a complete description of how to write your own functions. Upcoming issues of T-SQL Solutions will contain more in-depth articles about user-defined functions (UDFs).

Information Schema Views

Microsoft added information schema views in SQL Server 7.0 to meet ANSI specifications for standard SQL and added even more of these views in SQL Server 2000. Microsoft officially recommends that you use information schema views, rather than access information directly from system tables. In SQL Server 7.0, all databases have a set of information schema views. SQL Server 2000 contains only one set of information schema views—in the master database—but when you reference a view in master, the contents of the view are from the current database. For example, when you select from the TABLES view in the Pubs database, you get a list of table objects from Pubs.

A user called INFORMATION_SCHEMA owns all information schema views. Because you never log in as this user, you must always use the owner name to access these objects. You should always use an object's owner when referencing objects in production code, but for quick and dirty poking around, the practice can be awkward. For example, if I just wanted to see the names of the user tables in a database, I would usually write

SELECT name FROM sysobjectsWHERE type = 'U'

But officially, Microsoft recommends that you never directly select from any system table, so you can get the same information by using the TABLES information schema view. However, even though this view is called TABLES, it contains both tables and views. You have to add a WHERE clause to create a correct statement:

SELECT  TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'

This code is more difficult to write than the code that accesses system tables directly, and I admit that when I need simple data quickly, I still directly access the system tables.

Note that the two SELECT statements above are different. Although they might look like they return the same data (i.e., the names of all the user tables in a database), when you select from the TABLES view, you see only the tables you have permission for. As a systems administrator (sa) or database owner (DBO), you have full permission on all the tables. But typical users have access limitations. So, using information schema views doesn't always give you the same data that you get by directly accessing the system tables.

Information schema views are views, so they're SQL code. As you can with any other view, you can use the system procedure sp_helptext to see the SQL code definition. In the master database, when you execute sp_helptext on INFORMATION_SCHEMA.TABLES, you get the definition that Listing 4 shows. The view's SELECT statement accesses the system table sysobjects and qualifies the rows returned by verifying that the current user has permission to access the object.

You can improve your own system functions by using these special undocumented and underdocumented ways of working with system objects and by learning how to use bit arithmetic. Information schema views can also provide power for your functions. In the next T-SQL Admin column, I'll continue looking at information schema views, and I'll show you some ways to get useful information out of these views.