This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to create system objects which we can use for all SQL Server databases.
As a Database Administrator, I created few stored procedures for selecting data from the Master database to find out few important statistics.
I required calling the same stored procedure from the different databases, so I don’t like to create this kind of stored procedure in all databases, instead of that I chose to make it as System Object and open use for all Databases.
Create stored procedure in the Master database with the prefix “sp_”:
|
1 2 3 4 5 6 7 8 9 10 |
USE master GO CREATE PROCEDURE sp_AllDatabaseObjects AS SELECT name ,type_desc FROM sys.objects WHERE is_ms_shipped <> 1 |
Execute above stored procedure from another database:
|
1 2 3 4 |
USE AdventureWorks2012 GO EXEC sp_AllDatabaseObjects GO |
Another way is to explicitly mark the stored procedure as system object:
|
1 2 3 4 |
USE master GO EXEC sys.sp_MS_marksystemobject sp_AllDatabaseObjects GO |
After this mark, check the stored procedure, is system object or not:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT name ,is_ms_shipped FROM sys.objects WHERE name = 'sp_AllDatabaseObjects' /* The Result: name is_ms_shipped ------------------------- ------------- sp_AllDatabaseObjects 1 */ |
