从存储的proc定义中提取一个字符串

Question

我需要检查几个从远程服务器提取数据的存储进程所使用的库。

我已经(在SO的帮助下，参见SO 21708681)构建了以下代码：

DECLARE @tProcs TABLE
(
procID int IDENTITY,
procObjectID nvarchar(100),
procName nvarchar(100)
);
insert into @tProcs     
SELECT object_id, name 
FROM sys.objects 
WHERE  type in (N'P', N'PC') and name like '%_Extract'
declare @countProcs int, @I int=0
select @countProcs=COUNT(*) from @tProcs
while @I<@countProcs
Begin
declare @source_code nvarchar(max)
declare @objectID nvarchar(50)
declare @proc_Name nvarchar(200)
select @objectID=procObjectID from @tProcs where procID=@I
select @proc_Name=procName from @tProcs where procID=@I
select @source_code = definition
from sys.sql_modules
where object_id = @objectID 
SELECT PATINDEX('BOCTEST.%', @proc_Name) as Pos, @proc_Name 
-- or SELECT charindex(@source_code, '%BOCTEST%') 
set @I=@I+1
End

在每个目标存储的proc中都有一条这样的线：

DECLARE YP040P_cursor CURSOR FOR SELECT * FROM BOCTEST.S653C36C.LIVEBOC_A.YP040P

我需要知道每个存储过程的部件"LIVEBOC_A"(可以是"LIVEBOC_A"或"LIVEBOC _B")

我试图使用PATINDEX和CHARDINDEX从sysmodules中获取该字符串在定义中的起始opf的位置，但我得到的要么是零，要么是字符串或二进制数据将被截断的错误。

Answer 1

尝试

SELECT 
name, 
table_name = CASE WHEN OBJECT_DEFINITION(OBJECT_ID) LIKE '%BOCTEST.S653C36C.LIVEBOC_A.YP040P%' THEN 'LIVEBOC_A'
WHEN OBJECT_DEFINITION(OBJECT_ID) LIKE '%BOCTEST.S653C36C.LIVEBOC_B.YP040P%' THEN 'LIVEBOC_B' END
FROM sys.objects o
WHERE o.[type] IN ('P', 'PC')
AND name like '%_Extract'

Answer 2

您可以使用以下查询执行您想要的操作：

select name         = s.name + '.' + p.name ,
dt_created   = p.create_date ,
dt_modified  = p.modify_date ,
livboc_usage = case
when m.definition like '%declare%cursor%boctext.[^.].LIVEBOC_A.%' then 'A'
when m.definition like '%declare%cursor%boctext.[^.].LIVEBOC_B.%' then 'B'
else null
end
from sys.schemas     s
join sys.procedures  p on p.schema_id = s.schema_id
join sys.sql_modules m on m.object_id = p.object_id

但是，由于您要查找的是表的跨服务器依赖关系，您应该能够通过查询系统视图sys.sql_expression_dependencies来获得它，哪个

包含一行，用于当前数据库当一个实体(称为被引用实体)按名称出现在另一实体的持久化SQL表达式中，称为引用实体。例如，在定义中引用表时对于视图，作为引用实体的视图取决于表，即被引用的实体如果该表已删除，则该视图将不可用。

您可以使用此目录视图报告以下内容的依赖关系信息实体：

• 绑定到架构的实体
• 非架构绑定实体
• 跨数据库和跨服务器实体。报告实体名称；但是，实体ID没有被解析
• 绑定到架构的实体的列级依赖项。可以返回非架构绑定对象的列级依赖项通过使用sys.dm_sql_referenced_entities
• 在master数据库的上下文中时，会触发服务器级DDL

为此，在引用存储过程的数据库中运行这样的查询应该可以：

select name              = o.name      ,
type              = o.type_desc ,
liveboc_usage     = case d.referenced_schema_name
when 'liveboc_a' then 'A'
when 'liveboc_b' then 'B'
else                  null
end ,
has_dependency_on =       d.referenced_server_name
+ '.' + d.referenced_database_name
+ '.' + d.referenced_schema_name
+ '.' + d.referenced_entity_name
from sys.sql_expression_dependencies d
join sys.objects                     o on o.object_id = d.referenced_id
join sys.schemas                     s on s.schema_id = o.schema_id
where d.referenced_server_name   =    'BOCTEST'
and d.referenced_database_name =    'S653C36C'
and d.referenced_schema_name   like 'LIVEBOC_[AB]'
and d.referenced_entity_name   =    'YP040P'