sql-server - 如何跳过错误并继续运行SQL查询？

Question

我正在编写一段代码，目的是在服务器中的所有数据库中搜索某个表名，但是由于我没有读取/访问服务器中的所有数据库的权限，因此遇到了麻烦。

我想知道是否有一种方法可以前进，如果由于安全权限而导致一个语句不可用(即，如果我在特定服务器上有十个数据库而无法访问第四个，我希望它运行1-2个) -3，然后返回5-6-7-8-9-10(返回结果)。

我尝试使用TRY-CATCH，但是我似乎无法获得代码来绕过最初的问题，该问题在安全权限不可用时停止了。

declare @tabell varchar(254) = 'JE' -- table name which is supposed to be 
--found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by 
name) as rownumber into #steg1 from sys.databases

-- STEP 2: generates code for all databases in order to identify those 
--with the table name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if 
exists #hitta select * into #hitta from sys.tables where name = 
'''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select * from sys.tables 
where name = '''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end

收到错误消息:消息916，级别14，状态1，表明用户无法在当前安全上下文下访问数据库。

Answer 1

我设法使用has_dbaccess(database)解决了我的问题，下面您可以看到如何将其合并到代码中。

declare @tabell varchar(254) = 'JE' -- table name which is 
supposed to be found.

-- STEP 1: lists all available databases in the server with a row number.
drop table if exists #steg1 select name, row_number() over (order by name) as rownumber 
into #steg1 from (SELECT name, has_dbaccess(name) access FROM sys.databases) a where 
access = 1

-- STEP 2: generates code for all databases in order to identify those with the table 
--name @tabell.
drop table if exists #steg2 select 1 Ordn,'use '+name+' drop table if exists #hitta 
select name as [Table], cast('''+name+'''as varchar(max)) as [Databas] into #hitta from 
sys.tables where name = '''+ltrim(@tabell)+'''' as script into #steg2 from #steg1 a 
where rownumber =1
union
select 2 Ordn, 'use '+name+' insert into #hitta select name as [Table], 
cast('''+name+'''as varchar(max)) as [Databas] from sys.tables where name = 
'''+ltrim(@tabell)+'''' from #steg1 a
where rownumber >1
union
select 3 Ordn,'select * from #hitta' as x

-- STEP 3: concatenate the generated code into a single string.
declare @string varchar(max)
select @string = concat(@string + ' ', '')+ script from #steg2
drop table if exists #steg3 select @string as string into #steg3

-- STEP 4: exec the code concatenated in the previous step.
declare     @cmd    varchar(max)
begin 
set @cmd = (select string from #steg3)
exec (@cmd)
end