Run script on multiple DBs (SQL Server)?

Let’s say I have some update script:

update sometable set somecolumn = 'somevalue' where xyz = 0

Now let’s say I have multiple databases, like DB1, DB2, DB3 and so on. How could I run this script on all of them without doing it manually?

  • SQL Database with variable number of columns
  • SQL Server Schema Design
  • convert a flat database resultset into hierarchical object collection in C#
  • what datatype to use for a field that can contain a value from pre-defined values in a database table?
  • Reset identity seed after deleting records in SQL Server
  • Zend Framework 2, PDO dblib driver, how to change SQL Platform Decorator
  • Thanks 🙂

    2 Solutions collect form web for “Run script on multiple DBs (SQL Server)?”

    If you wanted all databases, you can use sp_MSforeachdb:

    EXEC sp_MSforeachdb @command1="UPDATE ?..sometable SET somecolumn='somevalue' WHERE xyz=0"

    Or for specific databases, you could try some of the logic as seen here:

    Hope that helps.

    You can do this using cursor

    • get list of all server in your lan or in network

    • create cursor for that

    • Than make use of sp_executesql to run you update script with forpart query

      set ANSI_NULLS ON
      CREATE PROCEDURE [sp_cross_db_query]
      @SQLQuery varchar(400)
      DECLARE @DB_Name varchar(100)
      DECLARE database_cursor CURSOR FOR 
      SELECT DatabaseName 
      FROM Management.dbo.Customers
      OPEN database_cursor
      FETCH NEXT FROM database_cursor INTO @DB_Name
              'USE [' + @DB_Name + '];' +
             FETCH NEXT FROM database_cursor INTO @DB_Name 
      CLOSE database_cursor 
      DEALLOCATE database_cursor

      to run the query

        exec sp_cross_db_query 'SELECT count(*) FROM Products'
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.