Creating INFORMATION_SCHEMA Views

for RuBoard

You may be aware that you can query ANSI-style INFORMATION_SCHEMA views from any database even though they actually reside only in master. The ability to create a view in the master database and to be able to query it in the context of the current database can come in quite handy. Imagine all the object duplication you could avoid if you could create views that behaved like system proceduresthat ran in the context of the current database even though they resided in master. As with most things SQL Server, if the system can create an object with special properties, so can you. The trick is in finding out how given that the precise technique usually undocumented. This is certainly the case with INFORMATION_SCHEMA views. The steps that I'm about to show you for creating them are not documented by Microsoft. As with all undocumented techniques, keep in mind that these steps may change or not work at all in future releases.

To create your own INFORMATION_SCHEMA view, follow these steps:

1. Enable updates to the system tables through a call to sp_configure:

    sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE 

2. Enable automatic system object creation by calling the undocumented procedure sp_MS_upd_sysobj_category (you must be the database owner or a member of the setupadmin role):

    sp_MS_upd_sysobj_category 1 

   This procedure turns on trace flag 1717 to cause all objects created to have their IsMSShipped bits turned on automatically. This is necessary because you can't create a nonsystem object that belongs to INFORMATION_SCHEMA. If you could, you could simply call sp_MS_marksystemobject (also covered in this chapter) after creating each view to enable its system bit. Instead, because SQL Server won't create nonsystem INFORMATION_SCHEMA objects, we have to enable a special server mode wherein each object created is automatically flagged as a system object.

3. Create your view in the master database, specifying INFORMATION_SCHEMA as the owner.

4. Disable automatic system object creation by calling sp_MS_upd_sysobj_category again:

    sp_MS_upd_sysobj_category 2 

5. Disable 'allow updates' by calling sp_configure:

    sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE 

That's all there is to it. You can create views that behave just like SQL Server's built-in INFORMATION_SCHEMA views. See Chapter 9 for some examples of user -defined system views.

for RuBoard