Find All the tables related to a stored procedure in Azure Synapse Datawarehouse

user_5359841 0
Viewed 339

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.

azure-synapse azure data-warehouse tsql
1 Answers

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.

user_15275040
Related