0

I have a query script tool which is run in multiple environments, with many in-script references to the database where the script is running. Rather than have to manually edit each of those instances at run-time, I'd like to set a variable at the top of the script so the database value can be assigned once.

I'd declare the variable so:

DECLARE @db VARCHAR(25) = 'some database name'

And then I'd like to use that variable in a reference to a table path, e.g.:

SELECT 1 FROM @db.sys.partitions

Is something like that possible? If so, how could it be constructed? Thanks.

juergen d
  • 201,996
  • 37
  • 293
  • 362
Greg
  • 15
  • 4
  • 2
    You can execute dynamic sql like this: `exec('SELECT 1 FROM ' + @db + '.sys.partitions')` – juergen d Dec 20 '13 at 14:49
  • Guys, thanks for the responses but I had trouble trying to use EXEC and SQLCMD since some of the references in my script are embedded in other functions. I ended up doing: DECLARE @db VARCHAR(25) = 'some database name' EXEC('USE ' + @db) and then just shortening the table paths. – Greg Dec 20 '13 at 15:58

1 Answers1

0

The following dynamic SQL should work for you:

declare @sql_command nvarchar(max)
declare @db varchar(25)
select @db = 'some database name'

select @sql_command = 'SELECT top 1 * FROM '+ @db + '.sys.partitions'
execute sp_executesql @sql_command
Seymour
  • 7,043
  • 12
  • 44
  • 51