Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Utility Procedures

By David Poole,

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
If you do not allow updates on system tables then you would have to use the following method.

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.

Condition Purpose
OBJECTPROPERTY(id,'IsUserTable')=1

We do not want to list system tables

OBJECTPROPERTY(id,'IsMSShipped')=0

This excludes tables such as dtProperties which is classed as a user table even though it is generated when a database diagram is created.

PERMISSIONS(id)!=0

The user must have at least some permissions to the table object in order to see the rowcount for the table. Tables where the user has no privileges simply won't be listed.

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

Total article views: 11927 | Views in the last 30 days: 7
 
Related Articles
FORUM

Create Login from master

Create login for a database from master

FORUM

Creating Database Objects from a Trigger-invoked stored procedure....

I am trying to create job-specific database objects (views, sp) after creating the db and tables suc...

FORUM

Create Login from master

Create login for a database from master

FORUM

Restore Master Database

Master Database

FORUM

dynamic object(database/schema/obj) in stored procedure

how to reference multiple database/objects in stored procedure (parameterization)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones