Friday, 12 December 2008

Write your own system stored procedure in SQL Server 2005 and 2008

I’ve found that writing stored procedures is always top of a DBA list of things to do in his or her spare time.

But sometimes I find that you write something so useful you want to use it on every database not just one, to do this you need to place it into the master database and here is how you do it

Use Master
--incase it already exists
IF OBJECT_ID('dbo.sp_pagelocks', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_pagelocks;
--my new procedure
CREATE PROCEDURE dbo.sp_pagelocks
select * from sys.indexes where allow_page_locks=0

you can now test it by going to another database and executing it.

Use mytestdb
Exec sp_pagelocks

You see things attached to the dbo on the master database are automatically accessible by all the database but remember that this works both ways, it means if you write a delete or table update statement it can be used on all database maybe even on one you don’t want to change things on so be careful what procedures you write into the master database.

Word to the wise try to use it only for system related procedures.

No comments: