查询表与存储过程的使用关系
1 模糊查询某个表和存储过程的关系，代码如下，参数表名称：BANK_INCTVA4，存储过程名称：sp_RiskGenMon
select distinct '['+object_name(id)+']' from syscomments where id in (
select object_id from sys.objects where type='P'
) and text like '%BANK_INCTVA4%' AND OBJECT_NAME(id) LIKE '%sp_RiskGenMon%'
2 存储过程依赖的表
select distinct
procedure_name = object_name(c.id)
,table_name = t.name
from sys.syscomments c inner join sys.tables t
on (c.text like '% ' + t.name + ' %'
or c.text like '%.' + t.name + ' %'
or c.text like '% ![' + t.name + '!] %' escape '!'
or c.text like '%.![' + t.name + '!] %' escape '!'
)
where c.id = object_id('sp_RiskGenMon00002_bak')
3 存储过程依赖的表，表依赖的文件导入情况
select * from TransFileLog where wrokdate='20150801'
and right(rtrim(filename),3)in(
select right(RTRIM(filename),3) from system_tblmaster where banktabname in(
select distinct table_name = t.name
from sys.syscomments c inner join sys.tables t
on (c.text like '% ' + t.name + ' %'
or c.text like '%.' + t.name + ' %'
or c.text like '% ![' + t.name + '!] %' escape '!'
or c.text like '%.![' + t.name + '!] %' escape '!'
)
where c.id = object_id('sp_RiskGenMon00002')
)
)
