查询同一服务器上的多个数据库(Querying multiple databases on the same server)

我需要查询服务器上的所有数据库。我浏览所有数据库并将每个数据库分配给变量@DB

然后我可以通过从游标中执行SQL来使用@DB，这一切都很好，直到我需要做另一个，如果存在来处理不包含我需要检查的状态的数据库。代码如下。

谢谢，

set nocount on use [master] declare @DB sysname declare @sql nvarchar(max) declare curDB cursor for select [name] from sysdatabases order by name create table #results ( [Database] nvarchar(max), [CompanyCode] nvarchar(max), [Title] nvarchar(max), [Status] nvarchar(max) ) use [master] open curDB fetch next from curDB into @DB while @@FETCH_STATUS=0 begin set @sql = 'use [' + @DB + ']; if exists(select 1 from sys.tables where name=''Licence'') begin Insert into #results Select DB_NAME() as ''Databas'', Company as ''CompanyCode'', Title as ''Title'' ' use @DB; --This is what i'm struggling with because the if exists check below is checking against master, i need it to check the current @DB from curDB if exists (select 'X' from sys.columns where name = 'DocumentLevelInvoiceMatching') begin set @sql = @sql + 'Case when Status = 1 then ''NewEngine'' else ''OldEngine'' end as ''MatchingEngine'' from dsdba.companies end ' end else set @sql = @sql + '''Old Engine'' from dsdba.companies end ' use master exec (@sql) fetch next from curDB into @DB end use CentralDatabase IF OBJECT_ID('dbo.CompanyConfiguration', 'U') IS NOT NULL DROP TABLE dbo.CompanyConfiguration; use master select * into CentralDatabase.dbo.CompanyConfiguration from #results Select * from CentralDatabase.dbo.CompanyConfiguration close curDB deallocate curDB drop table #results

I need to query all databases on a server. I cursor through all the databases and assign each next to a variable @DB

I can then use @DB by executing SQL from within the cursor and this is all fine until I need to do another if exists to handle databases which don't contain a status I need to check. Code below.

Thanks,

set nocount on use [master] declare @DB sysname declare @sql nvarchar(max) declare curDB cursor for select [name] from sysdatabases order by name create table #results ( [Database] nvarchar(max), [CompanyCode] nvarchar(max), [Title] nvarchar(max), [Status] nvarchar(max) ) use [master] open curDB fetch next from curDB into @DB while @@FETCH_STATUS=0 begin set @sql = 'use [' + @DB + ']; if exists(select 1 from sys.tables where name=''Licence'') begin Insert into #results Select DB_NAME() as ''Databas'', Company as ''CompanyCode'', Title as ''Title'' ' use @DB; --This is what i'm struggling with because the if exists check below is checking against master, i need it to check the current @DB from curDB if exists (select 'X' from sys.columns where name = 'DocumentLevelInvoiceMatching') begin set @sql = @sql + 'Case when Status = 1 then ''NewEngine'' else ''OldEngine'' end as ''MatchingEngine'' from dsdba.companies end ' end else set @sql = @sql + '''Old Engine'' from dsdba.companies end ' use master exec (@sql) fetch next from curDB into @DB end use CentralDatabase IF OBJECT_ID('dbo.CompanyConfiguration', 'U') IS NOT NULL DROP TABLE dbo.CompanyConfiguration; use master select * into CentralDatabase.dbo.CompanyConfiguration from #results Select * from CentralDatabase.dbo.CompanyConfiguration close curDB deallocate curDB drop table #results

最满意答案

您可以将检查移动到动态SQL中。

例

DECLARE @Qry NVARCHAR(MAX) = ' USE ' + QUOTENAME(@DB) + '; IF EXISTS (SELECT 1 FROM sys.columns...) BEGIN ... END ELSE BEGIN ... END '; EXECUTE(@Qry);

在动态sql之外， USE不能与变量结合使用，使得USE @DB语句无效。这是因为@DB是一个字符串（NVARCHAR（128）），而USE需要一个数据库。遗憾的是，您无法直接参数化数据库。

我添加了功能QUOTENAME来帮助防止SQL注入攻击。

You can move the check into your dynamic SQL.

Example

DECLARE @Qry NVARCHAR(MAX) = ' USE ' + QUOTENAME(@DB) + '; IF EXISTS (SELECT 1 FROM sys.columns...) BEGIN ... END ELSE BEGIN ... END '; EXECUTE(@Qry);

USE cannot be combined with a variable, outside of dynamic sql, making the statement USE @DB invalid. This is because @DB is a string (NVARCHAR(128)) and USE expects a database. Unfortunately you cannot directly parametrise databases.

I've added the functon QUOTENAME to help prevent SQL injection attacks.

更多推荐