azureazure-synapse

How to retrieve View definition on Synapse (Azure SQL DW)?

I am new to Synapse (Azure SQL DW). Currently, the DW has lots of views and I need to modify a handful of them. The issue is that I do not know how the views were created. Is there a query to check the view definition in Synapse or more specifically, the SELECT statement was used to create the view ?

Kind regards, Ken

Solution

  • sp_helptext is not supported in Synapse but you can use the view sys.sql_modules and its definition column to get the SQL text. A simple example, tested in a dedicated SQL pool:

    SELECT *
FROM sys.sql_modules
WHERE definition Like '%someColumn%'

    Main help page here. You can also use the function OBJECT_DEFINITION and pass it an object_id, eg

    SELECT OBJECT_DEFINITION( object_id ), *
FROM sys.views
WHERE is_ms_shipped = 0;