CODEWITHSUNDEEP
sqlsql-server
Ani
Ani

Reputation: 4523

Run update table query through all databases

I need the second query to run on the result of first. First query gives me all the databaseNames required for the second query

SELECT name 
FROM sys.databases where name like '%MySomething%'

And Second Query:

Update [databaseName].[someschema].[mytableSomething]
Set Flag = 1
Where Name = 'MyCondition'

So basically I need to update a column in a specific table in all the databases that it exists.

Upvotes: 0

Views: 1162

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Create Temp Table with the First Select Query, and Use While Loop through each Database name and use the Update the query with each database name in loop.

Psuedo code will be like this

SELECT name INTO #temprecords
FROM sys.databases where name like '%MySomething%'

Then use While loop 

counter = 1
   While loop for each record in temp table until counter <= total record in temp table
    Begin
         Get Database name in for counter 1
         Update query with each database name
       counter ++
     END

EDIT:- Your code will be like this

Declare @Count int, @Ini  int       
SELECT name,ROW_NUMBER() Over(Order BY database_id) as RowID
    INTO #temprecords
FROM sys.databases --where name like '%MySomething%'

SET @Count = (Select Count(*) from #temprecords)
SET @Ini = 1
While @Ini <= @Count
BEGIN
   Declare @dbname varchar(max),@qry varchar(max)
   SET @dbname = (Select name From #temprecords Where RowID = @Ini)

   SET @qry = 'Update '+ @dbname +'.[someschema].[mytableSomething]
   Set Flag = 1
   Where Name = MyCondition'

   --EXEC(@qry) --- uncomment this line one you check that @qry has correct query to update
   SET @Ini = @Ini + 1
END

Upvotes: 2

Related Questions