using dynamic query to alter / create database objects in all databases

  • 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.

  • 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

  • --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

  • That is what I did initially. It throws an error saying that "ALTER / CREATE" cannot have the database name in the query.

  • 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.

  • not open query. thats somethin else.

  • 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.

  • 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.

  • 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