SQL server – user defined system stored procedures

If you have written a stored procedure that you want’s to use in every database, you probably noticed that databased scoped objects will only return records from the database where you created the procedure, like sys.tables, sys.indexes, etc. If you want to create a system wide procedure to be able to collect information from any database, there is a undocumented procedure that marrks a procedure as “system” object. Take a look at the code below

USE master
go

-- do not name your procedure like this unless you plan to use it as a system procedure
CREATE PROCEDURE dbo.sp_procedure_test 
	@tableName sysname
AS
	SELECT * 
	FROM sys.[tables] AS t2
	WHERE @tableName LIKE @tableName
	AND is_ms_shipped=0
GO

--execute in master
EXEC dbo.sp_procedure_test 'a%'
GO

--change the database
USE [AdventureWorks2008R2]
GO
EXEC master.dbo.sp_procedure_test 'a%' -- you need to specify the master database

--as you can see, you still get tables from master

USE master
GO
--mark the procedure as system procedure
EXEC sp_ms_marksystemobject 'dbo.sp_procedure_test'

--change the database
USE [AdventureWorks2008R2]
GO
EXEC dbo.sp_procedure_test 'a%'

 
USE master
GO
DROP PROCEDURE dbo.sp_procedure_test

As you can see, the 2 first results still gets the tables from master database, but after using sp_ms_marksystemobject you can get the tables from the database where you are executing the procedure. This is very nice feature to be able to create user defined system procedures that are available from every database AND are using database scoped objects.

As with any undocumented feature, you have to use caution as they can be modified or deleted without notice. You should also keep in mind that if you create objects in master, you need to make sure you do not interfere with MS shipped system objects and you have backups of your code.

/Håkan Winther
Twitter: @h_winther