3

Is there a simple way to find out all the tables referenced in a stored procedure in azure analytics data warehouse other than parsing the stored procedure code? I tried few commands like sp_tables, sp_depends but none seems to be working in azure data warehouse.

bmsqldev
  • 2,627
  • 10
  • 31
  • 65

2 Answers2

4

sys.sql_expression_dependencies is supported in Azure Synapse Analytics, dedicated SQL pools, but only supports tables, views and functions at this time. A simple example:

SELECT * FROM sys.sql_expression_dependencies;

So you are left either parsing sys.sql_modules. Something like this is imperfect (ie doesn't deal with schema name, square brackets, partial matches etc) but could server as a starting point:

SELECT 
    sm.[definition],
    OBJECT_SCHEMA_NAME(t.object_id) schemaName,
    OBJECT_NAME(t.object_id) tableName
FROM sys.sql_modules sm
    CROSS JOIN sys.tables t
WHERE sm.object_id = OBJECT_ID('dbo.usp_test')
  AND sm.[definition] Like '%' + t.name + '%';

I actually use SQL Server Data Tools (SSDT) with dedicated SQL pools so your dependencies can't get out of step and are trackable via the project.

wBob
  • 13,710
  • 3
  • 20
  • 37
1
SELECT OBJECT_NAME(OBJECT_ID),definition 
FROM sys.all_sql_modules 
WHERE definition LIKE '%your_table_name%'
borchvm
  • 3,533
  • 16
  • 44
  • 45
Avishek
  • 795
  • 1
  • 6
  • 20