Pass a variable to fully qualified name as database in a SQL query

Question

Hi

I want to query across multiple databases in SQL Server, so I thought I could create a variable called @DBNAME and pass it to a query as follows (but it doesn't work).

This will be inside a loop where DBNAME is stepped through as a count on dbid.. my count works and it prints out @DBNAME as all of the production databases.. but I can't get it to query across multiple databases!

IF EXISTS (SELECT 1 FROM @DBNAME.INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='DataArea') SELECT 'GETIN' AS res ELSE SELECT 'aaargh' AS res --SELECT * From DataArea AS res ELSE SELECT NULL AS res;

Answer 1

You can do it with dynamic sql like this

declare @tablename as sysname, @dbname as sysname
select 
@dbname = 'master', 
@tablename = 'dbo.spt_fallback_db'
declare @script nvarchar(4000)
select @script = 'select top 10 * from ' + @dbname + '.' + @tablename
exec sp_executesql @script

Substitute your database and table names as appropriate. Just used one form master for testing purposes.