I produced a question that can take a database backup at certain specified location. I wish to utilize it like a saved procedure but this will behave as a worldwide saved procedure to ensure that whenever this SP is known as. Then database backup is taken.
It uses DB_Title() to consider database backup of owner database.
Can you really create such SP or Function.
I'm using sql server 2005
Should you make your sp within the master database and measure the level like a system object and prefix it with 'sp_' a single copy will exist that'll be shared by all databases.
and second solution from msdn:
Private and global temporary saved methods, similar to temporary tables, could be produced using the # and ## prefixes put into the process title. # denotes a nearby temporary saved procedure ## denotes a worldwide temporary saved procedure. These methods don't exist after SQL Server is shut lower.
a good 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'