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.