Monday, August 31, 2009

Get all MS SQL Server stored procedure dependencies


select distinct 'proc' =s7.name, 'name' = (s6.name+ '.' + o1.name),
type = substring(v2.name, 5, 66)
from sys.objects o1
,master.dbo.spt_values v2
,sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5 --11667
,sys.schemas s6
,sys.procedures s7

where o1.object_id = d3.depid
and o1.type = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
and u4.type = 'B' and u4.number = d3.resultobj
and w5.type = 'B' and w5.number = d3.readobj|d3.selall
and d3.id = s7.object_id
and o1.schema_id = s6.schema_id
and deptype < 2
order by s7.name, s6.name + '.' +o1.name

No comments:

Post a Comment