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.
declare @procText0 nvarchar(1000)
declare @procText1 nvarchar(4000)
declare @procText2 nvarchar(4000)
declare @procText3 nvarchar(4000)
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
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.