Sunday, 10 July 2016

Creating a stored procedure and convert in to a system object,access it from all other databases in SQL SERVER

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, 




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



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.



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

Above query will mark the object stored procedure as System object., we can check this by executing the following code.


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.