1

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.

Joseph
  • 530
  • 3
  • 15
  • 37

1 Answers1

0

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
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • When I executed above query, I get results only for View and not stored procedures, commenting the where condition was of no help either.I am using Azure Data Warehouse. – Joseph Nov 17 '17 at 09:40