March 4, 2010 at 10:41 am
Hi,
We are using sql server 2008. We have a separate database for each of our clients. Some of the stored procedures are same in all the databases. so when I change something in the stored procedure in one database I would like to apply that change to all the databases. Is there a script to do this? I tried using EXEC('ALTER ....') but it doesn't accept database name.
Thanks,
sridhar.
March 4, 2010 at 2:20 pm
you can loop through the dbs using the below then put the drop create of the sp in there.
set nocount on
DECLARE @sqlScript varchar( max )
Declare @db varchar(500)
declare @user varchar(100)
select name, 0 processed
into #dbs
FROM [master].[dbo].[sysdatabases]
where NAME NOT IN ( 'MASTER', 'msdb', 'model', 'tempdb','distribution')
WHILE EXISTS ( SELECT TOP 1 * FRoM #dbs WHERE PROCESSED = 0)
BEGIN
select top 1 @db = name from #dbs where processed = 0
set @sqlScript = 'PUT YOU DROP AND CREATE OF THE STORED PROC IN HERE' --KEEP IN MIND THIS WILL HAVE TO BE DYNAMIC SQL
exec( @sqlScript )
update #dbs
set processed = 1
where name = @db
END
drop table #dbs
March 4, 2010 at 2:47 pm
--notice how i have the local var @db and how it is added to the dynamic sql
set nocount on
DECLARE @sqlScript varchar( max )
Declare @db varchar(500)
declare @user varchar(100)
select name, 0 processed
into #dbs
FROM [master].[dbo].[sysdatabases]
where NAME NOT IN ( 'MASTER', 'msdb', 'model', 'tempdb','DBA','distribution')
WHILE EXISTS ( SELECT TOP 1 * FRoM #dbs WHERE PROCESSED = 0)
BEGIN
select top 1 @db = name from #dbs where processed = 0
set @sqlScript = 'drop '+@db+ '.the sp name goes here then u must have the new sp as a create script in here'
exec( @sqlScript )
update #dbs
set processed = 1
where name = @db
END
drop table #dbs
March 4, 2010 at 3:17 pm
That is what I did initially. It throws an error saying that "ALTER / CREATE" cannot have the database name in the query.
March 4, 2010 at 3:38 pm
sorry of course your right.
i think u can do it w/ open querry.
I did something like this the other day and ran into this same issue. i need to look around and find my solution.
March 4, 2010 at 3:41 pm
not open query. thats somethin else.
March 4, 2010 at 4:00 pm
i have a sol however it aint the least bit pretty however i'm going home so it will have to do for today.
in this sol u will have to have a proc in each db as such. Because this proc resides on the db u are changing the proc on it will work
===============
create proc aTestChanger
@sql varchar(1000)
as
exec (@sql)
========================
--this is the proc to be changed and it needs to reside in the same db as the above.
create proc aTest
as
selec 1
==========================================
declare @sql varchar(100)
declare @db varchar(100)
exec dba.dbo.atest --returns 1
set @db = 'dbadev'
set @sql = 'exec '+@db+'.dbo.aTestChanger ''alter proc dbo.atest as select 100''' -- in here u would put your alter as the input param and again because their in the same db it would not error
execute(@sql)
exec dba.dbo.atest --now returns 100 so the alter worked.
Again this is a long way around a short place but it's pretty savy in my opinion. The key is again u will need the aTestChanger proc on all dbs. Please post your solution when u find it if u come up w diff method.
March 4, 2010 at 4:07 pm
typo
declare @sql varchar(100)
declare @db varchar(100)
exec dbadev.dbo.atest --returns 1
set @db = 'dbadev'
set @sql = 'exec '+@db+'.dbo.aTestChanger ''alter proc dbo.atest as select 100''' -- in here u would put your alter as the input param and again because their in the same db it would not error
execute(@sql)
exec dba.dbo.atest --now returns 100 so the alter worked.
March 5, 2010 at 3:57 pm
if u dont want to do it that way u may be able to utilize the sp_foreachdb
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply