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?
Thanks :)
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'
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:
http://www.sqlservercurry.com/2009/04/6-common-uses-of-undocumented-stored.html
Hope that helps.
