我需要查询服务器上的所有数据库。 我浏览所有数据库并将每个数据库分配给变量@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 #resultsI 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.
更多推荐
发布评论