CODEWITHSUNDEEP
sqlsql-serverdatabasestored-proceduresfunction
Shantanu Gupta
Shantanu Gupta

Reputation: 21108

Is it possible to create a global stored procedure at Sql server level

I created a query that takes a database backup at certain specified location. I want to use it as a stored procedure but this should act as a global stored procedure so that whenever this SP is called. Then database backup is taken.

It uses DB_Name() to take database backup of owner database.

Is it possible to create any such SP or Function.

I am using sql server 2005

Upvotes: 13

Views: 14105

Answers (3)

Munavvar
Munavvar

Reputation: 821

There are 3 requirement for such stored procedure

  1. The stored procedure must be created in the master database.
  2. The name of the stored procedure must start with “sp_“.
  3. The stored procedure must be marked as a system object.

-- 1. Create the procedure in the master database

USE master
GO

-- 2. Create the procedure with the prefix sp_

CREATE PROCEDURE sp_[Stored_Procedure_Name]
AS
BEGIN
     -- Insert the logic of your stored procedure here
END
GO

-- 3. Mark the stored procedure as a system object

EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]

Upvotes: 1

CodeCowboyOrg
CodeCowboyOrg

Reputation: 3043

Three steps must be followed to create a "system" stored procedure that is accessible to all databases on the Server, as well as be able to run under the context of the current database when it is called.

  1. Master Database - The stored procedure should be created in the Master database
  2. Prefix Stored Procedure - The stored procedure name should be prefixed with sp_
  3. Mark SP as System Object - Call sp_ms_marksystemobject to mark custom SP as a system object

Example Code Below

--Step 1, Create in master database
USE master
GO

--Step 2, Prefix with sp_ the custom proc
CREATE PROCEDURE sp_myCustomSystemProc
AS
BEGIN
   PRINT 'myCustomCode'
END
GO

--Step 3, Mark as system object so proc executes in context of current db
EXEC sp_ms_marksystemobject 'sp_myCustomSystemProc'
GO

Upvotes: 7

masoud ramezani
masoud ramezani

Reputation: 22950

first solution:

If you create your sp in the master database and mark it as a system object and prefix it with 'sp_' then a single copy will exist that will be shared by all databases.

and second solution from msdn:

Private and global temporary stored procedures, analogous to temporary tables, can be created with the # and ## prefixes added to the procedure name. # denotes a local temporary stored procedure; ## denotes a global temporary stored procedure. These procedures do not exist after SQL Server is shut down.

an example :

    USE master
    CREATE TABLE test (c1 VARCHAR(50))
    INSERT test VALUES('master')
    go
    CREATE PROC sp_test AS
    SELECT * FROM test
    GO

USE northwind
    CREATE TABLE test (c1 VARCHAR(50))
    INSERT test VALUES('northwind')

USE pubs
    CREATE TABLE test(c1 VARCHAR(50))
    INSERT test VALUES('pubs')

USE pubs
    EXEC sp_test --returns 'master'

USE master
    EXEC sp_MS_marksystemobject sp_test

USE pubs
    EXEC sp_test --returns 'pubs'

USE northwind
    EXEC sp_test --returns 'northwind'

Upvotes: 19

Related Questions