Alternative of sp_depends in Azure Data Warehouse

Question

I need to get the list of tables used in a stored procedure,However in Azure Datawarehouse sp_depends is not supported.

The other alternative I thought of having is to get the stored proc code from INFORMATION_SCHEMA.ROUTINES and then run a script to get the [schema].[tablename] from the stored procedure definition but here the issue is in storing the whole stored proc into a variable. VARCHAR(MAX)has a limit of 8000 to store and if my proc exceeds this limit then I wont be able to get the complete table list.

Answer 1

Try using sys.sql_expression_dependencies. The following query may help you:

SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       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')
ORDER BY ReferencingObjectType, ReferencingObject