Sql-server – Change database and execute update query (T- Transact)

dynamic-sqlsql serversql-server-2012

Could anyone face with issue like me?

I need to update some rows in table, but I don't know exact name of database. I am able to figure out name with: 

Select name 
from sys.databases 
where name like '%%'

and then I need to execute update query, but I can't because I don't know how to force SQL server change database.

use "name of database"

update table
set column1 = column1 + 1

I tried to declare variable and set it as exact name of database. 

*EXEC ('use ' + '@dbname')
update table
set column1 = column1 + 1*

In this case, I got error about invalid object "table"

Does anyone know how to do it?

Best Answer

One solution would be to use SQL Servers sp_executeslql function. Utilising this function you could write some sql like the sample below. 

DECLARE @DatabaseToUse  VARCHAR(100),
        @SQLToRun       VARCHAR(200);

SELECT  @DateBaseToUse = 'MyDataBaseName';

SELECT @SQLToRun = 'UPDATE ' + @DatabaseToUse  + '.dbo.Table SET Column1 = Column1 + 1';

Execute sp_executesql @SQLToRun;

You should replace the .dbo. with the name of your Schema.

Related Question