Alter SP across databases

[jabrony76]

Hi all,

I have a stored procedure which is standard across 70+ databases on a server. Whenever I need to alter this procedure, I have been running the 'Alter Procedure [dbo].etc' in each database.

Is there any way I can automate this to run a mass update via a cursor or some other method?

FYI - The SP is large (3000+ lines)

Thanks

 

[mrdenny]

You can use sp_MSForEachDB to run the alter procedure on each database on the server.

exec sp_MSForEachDB 'use ?
alter procedure dbo.usp_something as
Code here
go'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vongrunt]

AFAIK sp_MSforeachdb has a limit of 2000 Unicode characters per statement... otherwise that's the way to go.

 

[jabrony76]

good idea, unfortunately have tried it already and the text is > 2000 chars... Any other thoughts?

Thanks!

 

[mrdenny]

Then how about a cursor against the sysdatabases table. As long as your proc is less than 7900 this will work for you.

declare @Proc as varchar(7900)
set @Proc = 'alter procedure usp_something as 
Code Here
go
'
Declare @Exec as varchar(8000)
declare @dbname as varchar(100)
declare cur CURSOR as select name from master.dbo.sysdatabases where name not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')
open cur
fetch next from cur into @dbname
while @@FETCH_STATUS = 0
BEGIN
  set @Exec = 'use ' + @dbname + '
' + @Proc
  exec (@Exec)
  fetch next from cur into @dbname
END
close cur
deallocate cur

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jabrony76]

Just got back to this... and again, the SP is > 8000 chars. Any other thoughts? Thanks all.
Andy

 

[mrdenny]

With a procedure over 8000 characters you are pretty much out of luck. The only data types that can hold over 8000 characters is text or ntext, but you can't use those in scripts as variables.

Denny

--Anything is possible. All it takes is a little research. (Me)