| Downloads |
|---|
| 25263.zip |
What makes an object a system object? In "Beyond Point-and-Click Administration," October 2001, InstantDoc ID 22070, I showed how you can create your own system stored procedures by supplying a name starting with the characters sp_ and creating the procedures in the master database. The sp_ naming convention not only lets you access the procedure from any database without specifying a database or object owner name, but in most cases, it directs system-table references to system tables in the current database instead of only to the master database in which the procedure was created. Then in "Investigating System Objects," January 2002, InstantDoc ID 23207, I showed you an undocumented and unsupported method for creating a system function owned by the special user system_function_schema. However, to create such an object, you have to reconfigure your SQL Server to allow direct updates to system tables, a move that requires great caution.
Now let's look at how to create a third type of system object—a view that you create only once and that you can easily access from any database. Microsoft supplies the information schema views in SQL Server, and in SQL Server 2000, these objects exist only in the master database. However, like system stored procedures, you can access information schema views from any database without qualifying the object with the database name or the object owner name. So how can you create such views of your own?
In the same way that you can create your own stored procedures, you can create views that have the special prefix sp_. (For more information about using sp_, see the sidebar "Versatile Sp_.") For example, Listing 1 shows the code you can use to create the view sp_myprocs in the master database. You can reference this view, which accesses all the stored procedures from the sysobjects table, from any database. However, a view you create with sp_ has only some of the special abilities that procedures with that prefix have. Although a view with the sp_ prefix has the special property of being available to any database, the tables it references don't change. Because I created the sp_myprocs view in the master database, the only table that sp_myprocs can access is the sysobjects table in the master database—no matter what database you're currently in. This isn't the way the SQL Server information schema views behave.
Creating a view that behaves like a SQL Server 2000 information schema view is relatively straightforward. Two similar methods exist for creating such a view: You can create a new view with the owner INFORMATION_SCHEMA, or you can modify an existing information schema view. The Microsoft-supplied information schema views have the owner INFORMATION_SCHEMA, but if you try to create a view that has that owner, as the code in Listing 2 attempts, you get the following error message:
Server: Msg 2760, Level 16, State 1, Procedure procs, Line 3 Specified owner name 'INFORMATION_SCHEMA' either does not exist or you do not have permission to use it.
As you do when you create a system function by using the special username system_function_schema, you can create a view that has the owner INFORMATION_SCHEMA if you first allow updates to system tables. Change the allow updates configuration value to 1, as the following code shows:
USE master GO EXEC sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE GO
After you've made this configuration change, you can use the code in Listing 2 to create a view that has the owner INFORMATION_SCHEMA. This view is accessible from any database and references the sysobjects table in the database from which it's called. You can use the following code to verify that the view works:
USE pubs SELECT * FROM INFORMATION_SCHEMA.procs GO USE Northwind SELECT * FROM INFORMATION_SCHEMA.procs GO
When you've finished creating and testing the view, you need to remember to reset the allow updates configuration value to 0. However, if you need or want to drop this procedure, you'll have to reset the allow updates configuration value to 1.
As I've mentioned in previous columns, Microsoft discourages direct updates to the system tables. If you're leery of changing the allow updates configuration option, one more solution for creating a system view is available. Although you must allow system-table updates to create an object owned by INFORMATION_SCHEMA, you don't need to allow such updates to change the object owner to INFORMATION_SCHEMA. You can create the view in the master database by using the default owner (probably DBO), then change the owner to INFORMATION_SCHEMA, as the code in Listing 3 shows.
One benefit of being able to create your own system views is that you can create modified versions of the information schema views that Microsoft supplies with SQL Server. For example, the Microsoft information schema view called TABLES contains more columns than I usually want to see when I need only a quick list of user tables. So I could write a query that directly accesses the sysobjects table—or I could create a view called USERTABLES that behaves like an information schema view. I could write the code either by modifying the current TABLES view code or by creating a view that accesses the TABLES view, as Listing 4 shows.
For a more complex view, I might choose the option of modifying existing information schema view code. You can access the code by using the sp_helptext stored procedure or through Enterprise Manager. Alternatively, the code for all the supplied information schema views is available in the file called ansiview.sql in the \INSTALL directory of your SQL Server.
I prefer to create my own information schema views by changing the view's owner after I create the view. Thus, I'm really not doing anything that Microsoft discourages, such as allowing direct updates to the system tables. Of course, my view's code might directly access system tables, and that is discouraged. But I never let a little discouragement stop me. If I can use the system objects that Microsoft provides to create more and better tools, I don't hesitate to use them.
