Using a Variable to Construct a Table Path [duplicate]

Question

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.

You can execute dynamic sql like this: exec('SELECT 1 FROM ' + @db + '.sys.partitions') — juergen d
– juergen d, Commented Dec 20, 2013 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
– Greg, Commented Dec 20, 2013 at 15:58

Seymour · Accepted Answer · 2013-12-20 15:03:47Z

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

Collectives™ on Stack Overflow

Using a Variable to Construct a Table Path [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related