Retrieve Database name from an variable

Question

I have a simple requirement where I need to replace a variable with the database name coming from another table.

I have a table called config setting

Key value

ClubcardDB ClubardDetailsDB

Code

Declare @Value varchar(100)
select @Value= value configsetting where Code=’ ClubcardDB’
Select top 10 * from  [@Value].[dbo].[ClubcardDetails]

it is giving an error: Invalid object name '@Value.dbo.ClubcardDetails'.

I do not want to build and dynamic query..

You would need dynamic sql for this. – Sean Lange Mar 11 '19 at 13:56 — Sean Lange, Mar 11 '19 at 13:56
Why did not you want to build dynamic query – D-Shih Mar 11 '19 at 14:04 — D-Shih, Mar 11 '19 at 14:04

UnhandledExcepSean · Answer 1 · 2019-03-11T14:18:28.877

0

Here is a working example that I made on my machine:

Declare @Value varchar(100)='Test'
DECLARE @SQL NVARCHAR(MAX)

SET @SQL='Select * from ' + QUOTENAME(@Value) + '.[sys].[all_columns];'
EXECUTE sp_executesql @SQL

A slightly safer method as it verifies the value is actually a database name:

Declare @Value varchar(100)='Test'
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL='Select * from ' + QUOTENAME(name) + '.[sys].[all_columns];'
FROM master.dbo.sysdatabases
WHERE name=@Value

EXECUTE sp_executesql @SQL

edited Mar 11 '19 at 14:18

answered Mar 11 '19 at 14:06

UnhandledExcepSean

12,504
2
35
51

You'll want to at least handle SQL Injection here. This is just a security vulnerability as it is. – Thom A Mar 11 '19 at 14:08
While that is true, if you can't control the value in your own configuration table, you have bigger issues. – UnhandledExcepSean Mar 11 '19 at 14:09
2

I still suggest properly using `QUOTENAME` at least. A bracket can easily be escaped by someone malicious. For example `SET @Value = N'master].sys.objects; CREATE LOGIN t WITH PASSWORD=''1'',CHECK_POLICY=OFF,CHECK_EXPIRY=OFF;--'` – Thom A Mar 11 '19 at 14:10
Really? What didn't work when you tried `'...FROM ' + QUOTENAME(@value) + '.sys...'`? – Thom A Mar 11 '19 at 14:18
@Larnu I didn't remove the [] in the literal; answer updated – UnhandledExcepSean Mar 11 '19 at 14:18

score 0 · Answer 2 · answered Mar 11 '19 at 14:09

0

Try to use SQLCMD Mode. In SSMS, click Query -> SQLCMD Mode:

:setvar dbname "YourDB" 

SELECT * FROM $(dbName).dbo.YourTable

Retrieve Database name from an variable

2 Answers2