Wednesday, October 8, 2014

List procedures and their dependent tables

select

distinct s1.name as 'Procedure Schema Name', o1.name as 'Procedure Name', s2.name as 'Table Schema Name', o2.name as 'Table Name' from sysdepends d


join sys.objects o1 on d.id = o1.object_id


join sys.schemas s1 on o1.schema_id = s1.schema_id


join sys.objects o2 on d.depid = o2.object_id


join sys.schemas s2 on o2.schema_id = s2.schema_id


where


o1.type = 'P' and


o2.type = 'U'

order

by 1,2,3,4

No comments:

Post a Comment