Run script on multiple DBs(SQL Server)?

Question

Hello, lets say I have some update script:

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

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

Thanks :)

Answer 1

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'
  

Answer 2

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.