Monday, December 29, 2014

Sql Azure : Way Around for Sp_depends in Sql Azure

You can use following query to find out table used in Stored procedures similar to sp_depends.

Sp_depends helps us to provide information where sql object in referred in Views, stored procedure.
sp_depends is not available in sql azure but following query will provide similar functionality.
SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.',
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   sys.sql_expression_dependencies ed
       INNER JOIN  sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF') and ed.referenced_entity_name like '<Table Name>'
ORDER BY ReferencingObjectType, ReferencingObject

replace <Table name>  with your actual table name which you want to search

Happy Coding !!

No comments:

Post a Comment