shared stored procedure

Question

Let's say I have many sql server databases which have exactly the same schema. Is there any place where I can write a stored procedure and apply for all databases? If I create the stored procedure for each database, when needed, I have to update them all.

Answer 1

The issue you'll run into is table bindings -- how will the sproc know which database to call? So you'll probably want to use marc_s's comment plus some dynamic SQL where you pass in the database name (and username) to the shared sproc.

create proc GetAllWidgets @dbname sysname, @owner nvarchar(100)
as
declare @sql nvarchar(4000) 
set @sql = 'select * from [' + @dbname + '].[' + @owner + '].Widget'
sp_executesql @sql

Answer 2

You could use a central management server and run your alter/create script against multiple servers at the same time.See msdn

Answer 3

You can create a stored procedure in master that runs in the current database context if you prefix the stored procedure with sp. Here's an example:

USE master
GO
CREATE DATABASE Test1  --for demo purposes
GO
CREATE PROCEDURE dbo.sp_DoStuff
AS
SET NOCOUNT ON
SELECT DB_NAME()
GO

USE Test1
GO
EXEC dbo.sp_DoStuff
GO

USE msdb
GO
EXEC dbo.sp_DoStuff
GO

USE master
GO
DROP DATABASE Test1
DROP PROCEDURE dbo.sp_DoStuff
GO

This of course is why you don't normally create user stored procs with starting sp... the engine attempts to resolve the name in master

You can rely on deferred name resolution to not throw an error for your non-existent tables in master