0

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?

0

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.

| improve this answer | |
  • Thanks for fixing my typo. I read it twice before posting and I didn't notice :-) – armitage Apr 24 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.