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

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    GOUSE 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_testUSE pubs    EXEC sp_test --returns 'pubs'USE northwind    EXEC sp_test --returns 'northwind' 

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 databaseUSE masterGO--Step 2, Prefix with sp_ the custom procCREATE PROCEDURE sp_myCustomSystemProcASBEGIN   PRINT 'myCustomCode'ENDGO--Step 3, Mark as system object so proc executes in context of current dbEXEC sp_ms_marksystemobject 'sp_myCustomSystemProc'GO

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 masterGO

-- 2. Create the procedure with the prefix sp_

CREATE PROCEDURE sp_[Stored_Procedure_Name]ASBEGIN     -- Insert the logic of your stored procedure hereENDGO

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

EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]