在SQL查询中将变量作为数据库传递给完全限定名

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).

我想查询SQL Server中的多个数据库,所以我想我可以创建一个名为@DBNAME的变量并将其传递给查询,如下所示(但它不起作用)。

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!

这将在一个循环中,其中DBNAME作为dbid的计数逐步执行..我的计数工作,它打印出@DBNAME作为所有生产数据库..但我不能让它跨多个数据库查询!

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

IF EXISTS(从@ DBNAME.INFORMATION_SCHEMA.TABLES中选择1)

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;

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

1 个解决方案

#1

You can do it with dynamic sql like this

你可以用这样的动态sql来做

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.

根据需要替换您的数据库和表名。只是使用一个表单主机进行测试。

#1