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?

  • Is my database good for hierarchical data? I want to write a SQL query it with “Where” value
  • Export Sql Server (64 bit) to Excel (32 bit)
  • How do select a string greater then 8k characters out of a SQL Server 2008 XML field
  • what datatype to use for a field that can contain a value from pre-defined values in a database table?
  • C# SqlCommand syntax for this SQL command
  • Replace character in whole table in SQL Server
  • Thanks 🙂

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

    If you wanted all databases, you can use sp_MSforeachdb:

    http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

    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:

    http://www.sqlservercurry.com/2009/04/6-common-uses-of-undocumented-stored.html

    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
      set QUOTED_IDENTIFIER ON
      GO
      
      CREATE PROCEDURE [sp_cross_db_query]
      @SQLQuery varchar(400)
      AS
      
      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
      
      WHILE @@FETCH_STATUS = 0 
      BEGIN 
          exec(
              'USE [' + @DB_Name + '];' +
              @SQLQuery
              )
             FETCH NEXT FROM database_cursor INTO @DB_Name 
      END
      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.