Thursday, November 26, 2009

How to create a procedure in all databases in a server?

I had a situation where I wanted to create a stored procedure in all databases for a specified SQL server.

I created a below code for that purpose.


EXEC sp_msForEachDB
'
USE ?
declare @procText0 nvarchar(1000)
declare @procText1 nvarchar(4000)
declare @procText2 nvarchar(4000)
declare @procText3 nvarchar(4000)
if ''?''<>''model''
begin
      print ''?''
      set @procText0=''
      If (select objectproperty(object_id(''''dbo.PROC_NAME''''),''''IsProcedure''''))=1
            drop proc dbo.PROC_NAME
       ''
      select @procText1= text from model.sys.syscomments where id=object_id(''model.dbo.PROC_NAME'') and colid=1
      select @procText2= text from model.sys.syscomments where id=object_id(''model.dbo.PROC_NAME'') and colid=2
      select @procText3= LTRIM(RTRIM(text)) from model.sys.syscomments where id=object_id(''model.dbo.PROC_NAME'') and colid=3
      EXEC (@procText0)
      EXEC (@procText1+@procText2+@procText3)
end
'

First of all you need to create the stored procedure in one database of your choice. In this case I decided to go with the “model” database which is a template db for any new database.

I have created three nvarchar variables to hold the procedure text. The variable “@procText0” has the code to check the existence and drop if it exists.  The remaining three variables are only for procedure text.

No of variables needed dependent upon how big is your procedure. If it contains less than 4000 (considering nvarchar data type) characters then you need only single variable.

If you've a different solution please share with me. 

No comments:

Post a Comment

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...