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
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.
根据需要替换您的数据库和表名。只是使用一个表单主机进行测试。