In this post we are going to see how to convert a stored procedure in to system object and access it in all other databases in Sql server.
Why we need a system objects, because it will be accessible for all databases in there selected database context. For example if we want a stored procedure which have general logic and applicable or can be run in all database then we have to kept it in some common place so any one can use it. Now we will create a something like that one.
We will create a stored procedure which will list out the tables present in the database,
Above stored procedure will give a result of tables present in the database, but now we have to access it from all database so we have to compile this stuff in Master Database, For testing purpose i am creating a table in master database "Master_History" , Now this stored procedure can be access from all database.
After compile the stored procedure we will test the sp, by executing it from different databases.
So first we will create a new database named "Rajesh" and create a one sample table to test the SP.
Now we will test the Stored procedure by executing it.
When we see the result set of this query, we will wonder because both the execution gives the same result set, but we are running the sp from individual database, for second execution we must have only one record with value of Employee table , but instead of showing that it is showing the same result of master database, i.e the Stored procedure is executing in Master database instead of "Rajesh" Database, so now we are going to fix this by marking the object as System object , it will work like the object of that selected database context., To mark a Object as System object , we should use the Master database, because our object is present in that context
Above query will mark the object stored procedure as System object., we can check this by executing the following code.
Again we will test the Stored procedure by pointing out it from different databases, now we see what is happening.
After marking the object as System object , the Stored procedure is working correctly.From this post you can learn how to create a stored procedure and convert in to a system object , access it from all other database in Sql Server.
Why we need a system objects, because it will be accessible for all databases in there selected database context. For example if we want a stored procedure which have general logic and applicable or can be run in all database then we have to kept it in some common place so any one can use it. Now we will create a something like that one.
We will create a stored procedure which will list out the tables present in the database,
CREATE PROCEDURE SP_GET_TABLES
AS
BEGIN
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'U'
END
Above stored procedure will give a result of tables present in the database, but now we have to access it from all database so we have to compile this stuff in Master Database, For testing purpose i am creating a table in master database "Master_History" , Now this stored procedure can be access from all database.
USE MASTER
GO
CREATE TABLE MASTER_HISTORY(ID INT,OBJNAME VARCHAR(200))
GO
CREATE PROCEDURE SP_GET_TABLES
AS
BEGIN
SELECT * FROM SYS.OBJECTS WHERE TYPE = 'U'
END
So first we will create a new database named "Rajesh" and create a one sample table to test the SP.
CREATE DATABASE RAJESH
Go
USE RAJESH
GO
CREATE TABLE EMPLOYEE(ID INT, NAME VARCHAR(30))
GO
Now we will test the Stored procedure by executing it.
USE MASTER
GO
SELECT DB_NAME() AS 'dbname'
EXEC SP_GET_TABLES
USE RAJESH
GO
SELECT DB_NAME() AS 'dbname'
EXEC SP_GET_TABLES
When we see the result set of this query, we will wonder because both the execution gives the same result set, but we are running the sp from individual database, for second execution we must have only one record with value of Employee table , but instead of showing that it is showing the same result of master database, i.e the Stored procedure is executing in Master database instead of "Rajesh" Database, so now we are going to fix this by marking the object as System object , it will work like the object of that selected database context., To mark a Object as System object , we should use the Master database, because our object is present in that context
USE MASTER
GO
EXEC SYS.SP_MS_MARKSYSTEMOBJECT 'SP_GET_TABLES'
GO
SELECT NAME, IS_MS_SHIPPED
FROM SYS.OBJECTS
WHERE NAME = 'SP_GET_TABLES'
GO
Again we will test the Stored procedure by pointing out it from different databases, now we see what is happening.
USE MASTER
GO
SELECT DB_NAME() AS 'dbname'
EXEC SP_GET_TABLES
USE RAJESH
GO
SELECT DB_NAME() AS 'dbname'
EXEC SP_GET_TABLES
After marking the object as System object , the Stored procedure is working correctly.From this post you can learn how to create a stored procedure and convert in to a system object , access it from all other database in Sql Server.