Tips for installing utility procedures and views

Most DBAs will have built up an assorted toolkit of procedures and views that they use to make life easier for themselves. These objects are generically useful and therefore need to be accessible in all user databases.

Now the DBA could install these objects in all the existing user databases and in the MODEL database to ensure that future databases come with those objects predefined however there is an alternative.

INFORMATION_SCHEMA views

SQL Server 2000 comes with a number of special views called INFORMATION_SCHEMA views. These reside in the MASTER database.

They are special because although they reside in the MASTER database when a SELECT statement is carried out on those views the records that are returned are for in the context of the current database. That is if you run a SELECT on INFORMATION_SCHEMA.TABLES in the pubs database you will get information on the tables and views in the pubs database. If you run it in Northwind then you will get the tables/views relevant to Northwind.

The DBA can utilise this behaviour to add some of their own favourite queries.

Before we go any further it should be pointed out that adding objects to the MASTER database is generally frowned upon. If you are going to make use of the techniques described here then the following precautions need to be made.

• Ensure that the MASTER database is backed up before you proceed
• Ensure that the MASTER database is backed up after you finish
• Ensure that your objects have names that cannot clash with existing SQL Server object names. More on naming conventions later.
• Ensure that the scripts to create your utility objects are stored somewhere safe, preferrably under source control
• Always remove the utility objects before hot fixing or service packing SQL Server

Creating an INFORMATION_SCHEMA view

Creating an INFORMATION_SCHEMA view is only slightly more involved than creating any other view. There are a few caveats to bear in mine.

Firstly, with the appropriate create permissions anyone can create a view in the MASTER database.

Secondly anyone who can run the sp_changeobjectowner system stored procedure can change the view into an INFORMATION_SCHEMA view.

What they cannot do, unless you allow updates on system tables, is DROP or ALTER those objects.

The example below shows a script for creating a INFORMATION_SCHEMA view to show the estimated row count for every user table in a database to which the user has at least some perrmissions.

USE MASTER
GO
-- Allow updates on system tables
exec sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
GO
-- If an existing version of the view already exists then drop it
IF EXISTS(SELECT 1 FROM SysObjects WHERE Type='V' AND Name='TABLE_ESTIMATED_ROWS')
DROP VIEW INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS
GO

CREATE VIEW INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS
AS
SELECT object_name(id) as TableName,
MAX(rowcnt) AS EstimatedRows
FROM dbo.sysindexes 
WHERE indid<2 -- clustered index or table entry
AND OBJECTPROPERTY(id,'IsUserTable')=1
AND OBJECTPROPERTY(id,'IsMSShipped')=0
AND PERMISSIONS(id)!=0 -- User must have at least some privileges on the object.
GROUP BY object_name(id)
GO
-- Grant SELECT rights to the view for the PUBLIC role
GRANT SELECT ON INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS TO PUBLIC
GO
-- Disallow updates on system tables.
exec sp_configure 'Allow Updates',0
RECONFIGURE WITH OVERRIDE
GO

Dissecting the script

To be able to create an INFORMATION_SCHEMA view directly you must temporarily allow updates on system tables within the MASTER database.The statements that do so are:

exec sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
GO

Correspondingly when the view has been created you must switch off the facility to allow updates on system tables.

exec sp_configure 'Allow Updates',0
RECONFIGURE WITH OVERRIDE
GO

CREATE VIEW dbo.TABLE_ESTIMATED_ROWS
AS
SELECT object_name(id) as TableName,
MAX(rowcnt) AS EstimatedRows
FROM dbo.sysindexes 
WHERE indid<2 -- clustered index or table entry
AND OBJECTPROPERTY(id,'IsUserTable')=1
AND OBJECTPROPERTY(id,'IsMSShipped')=0
AND PERMISSIONS(id)!=0 -- User must have at least some privileges on the object.
GROUP BY object_name(id)
GO
exec sp_changeobjectowner 'TABLE_ESTIMATED_ROWS','INFORMATION_SCHEMA'
-- Grant SELECT rights to the view for the PUBLIC role
GRANT SELECT ON INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS TO PUBLIC
GO

Without allowing updates on system tables though you cannot:

• Reassign ownership of the object
• DROP the object
• ALTER the object

If you open the object within Enterprise Manager you will see that the CREATE VIEW statement still has the reference to dbo.TABLE_ESTIMATED_ROWS and any attempt to correct it will result in an error message.

The method of getting a fast row count, which is what the view provides, has cropped up in various forms on SQLServerCentral over the years so I won't discuss it here. However, the last three conditions in the WHERE statement are important.

OBJECTPROPERTY(id,'IsUserTable')=1

OBJECTPROPERTY(id,'IsMSShipped')=0

PERMISSIONS(id)!=0

When the view has been successfully created then it is simply a case of running the following in the user database.

SELECT * FROM INFORMATION_SCHEMA.TABLE_ESTIMATED_ROWS

Utility stored procedures

If a stored procedure name begins with sp_ then SQL Server will look in the MASTER database first for that procedure before falling back to the user database. This is why it is considered bad practice to have stored procedure names beginning with sp_.

In the case of utility procedures we can use this behaviour to our advantage to make our stored procedure accessible as any other system stored procedure.

Because there is the risk of clashing with a Microsoft stored procedure it is wise to come up with a naming convention that is extremely unlikely to clash. I have used sp_MyCompany as a suitable naming prefix. If you work for a company called AutoStats then I strongly suggest you use some other convention!

An example utility procedure

The example below shows a stored procedure that will mark all objects of a specific type for recompilation.

USE MASTER
GO
IF EXISTS (SELECT 1 FROM SysObjects WHERE Type='P' AND Name='sp_MyCompany_Recompile')
DROP PROC dbo.sp_MyCompany_Recompile
GO
CREATE PROC dbo.sp_MyCompany_Recompile
@ObjectType CHAR(1)='P' --P = (default) Procedures, T = Triggers, U = User Tables
AS
SET NOCOUNT ON
-- Only allow SQL Server System Administrators to run this procedure.
IF IS_SRVROLEMEMBER('SysAdmin')=0
BEGIN
RAISERROR (15003,-1,-1,N'SysAdmin')
RETURN
END
DECLARE @ObjectName sysname ,
@sSQL VARCHAR(200)
SET @ObjectName=''
WHILE @ObjectName IS NOT NULL
BEGIN
SELECT @ObjectName = MIN(Name)
FROM dbo.sysobjects
WHERE Type=CASE @ObjectType
WHEN 'U' THEN 'U'
WHEN 'T' THEN 'TR'
WHEN 'P' THEN 'P'
ELSE 'P' END
AND Name > @ObjectName
AND OBJECTPROPERTY(Id,'IsMSShipped')=0
IF @ObjectName IS NOT NULL
BEGIN
exec sp_recompile @ObjectName
END
END
GO
GRANT EXECUTE ON sp_MyCompany_Recompile TO public

There is nothing clever about the stored procedure itself however it is worth drawing attention to the following lines.

-- Only allow SQL Server System Administrators to run this procedure.
IF IS_SRVROLEMEMBER('SysAdmin')=0
BEGIN
RAISERROR (15003,-1,-1,N'SysAdmin')
RETURN
END

If the person calling the procedure is not a member of the SysAdmin role then the procedure will not run and they will receive a message saying

Msg 15003, Level 16, State 1, Line 1
Only members of the SysAdmin role can execute this stored procedure.

This is a double safety-check so because in theory any use could execute this command. This is discussed below.

The mechanism that allows the procedure to work

There is a special user in the MASTER and TEMPDB databases called GUEST. This user is a special user for the following reasons

• It is not associated with a login
• Any SQL Server user with a valid login will have access to any database in which a GUEST user exists. This is why privileges should never be granted to a GUEST user and why GUEST users should be removed from user databases and the MODEL database.
• GUEST and indeed all users are members of the PUBLIC role. This is why direct permissions should not be given to the PUBLIC role

In common with many system stored procedures we have granted EXECUTE permissions for our utility procedure to the PUBLIC role precisely because it is supposed to be accessible to a GUEST account via the PUBLIC role.

This is also why we have the check to make sure that the executing user is a System Administrator.

Other considerations when creating utility procedures

You cannot use INFORMATION_SCHEMA views in utility stored procedures residing in the MASTER database.

Where as a utility procedure that does a SELECT on dbo.sysobjects will hit the table in the current user database one that uses a INFORMATION_SCHEMA view will only access information from the MASTER database.

Pros and Cons of putting utility procedures in the MASTER database

Pros

• Objects are in only one database therefore maintenance is straight forward
• Unless someone trawls through the MASTER database the existence of the procedures is hidden. What people don't see they don't fiddle with.
• Objects are accessible throughout the server. This is very useful when you have a large number of databases

Cons

• Care must be taken not to conflict with Microsoft shipped objects
• Before patching a SQL Server utility objects should be removed from the server
• If objects are to be removed then the scripts to regenerate the objects must be available
• As these procedures are globally available
  • Security checking within the objects must be thorough
  • Testing of the stored procedures must be extremely thorough

Conclusion

As stated at the beginning of this article, it is generally frowned upon to add objects into the MASTER database however an experienced DBA will know when rules can be bent and even broken.

Because it is such an important point it is worth repeating that

if you are going to make use of the techniques described here then the following precautions need to be made.

• Ensure that the MASTER database is backed up before you proceed
• Ensure that the MASTER database is backed up after you finish
• Ensure that your objects have names that cannot clash with existing SQL Server object names. More on naming conventions later.
• Ensure that the scripts to create your utility objects are stored somewhere safe, preferrably under source control
• Always remove the utility objects before hot fixing or service packing SQL Server