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 own开发者_如何学JAVAer database.
Is it possible to create any such SP or Function.
I am using sql server 2005
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'
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.
- Master Database - The stored procedure should be created in the Master database
- Prefix Stored Procedure - The stored procedure name should be prefixed with sp_
- 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
There are 3 requirement for such stored procedure
- The stored procedure must be created in the master database.
- The name of the stored procedure must start with “sp_“.
- 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]
精彩评论