Can I run one script in multiple sql databases at once ?

Posted on 2012-08-27
Last Modified: 2012-08-27
I have a server with over 50 databases, they are all exactly the same, for liability reasons we need to keep them separate. Every week or so I need to run a script to update them, it is the same script for all 50 databases.
Is there a way to run the script ONCE and have it update ALL selected databases ?  It is a pain to have to run it 50 times.
I am using MS SQL 2008 RC2.
Question by:amucinobluedot
    LVL 2

    Accepted Solution

    yes you can do this.
    use the below script to loop through database.

    DECLARE @name VARCHAR(50) -- database name  
    DECLARE db_cursor CURSOR FOR  
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')  
    order by name

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  

          print ' Starting ...' + @name
          --your code  goes here
          FETCH NEXT FROM db_cursor INTO @name  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    Author Comment

    What if I want to select which databases to run it on ?
    There are a few databases there that are not related to this one particular service and those need to remain untouched.
    LVL 2

    Expert Comment

    you can avoid that database in this line

    WHERE name NOT IN ('master','model','msdb','tempdb')

    Author Comment

    Thanks .. I actually found the perfect tool. RedGate has 'multi script', allows to run scripts in multiple databases and actually multiple sql servers as well. Will make managing the lists of databases easier !

    Thanks though !


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now