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

Question

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

Son

Answer 1

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;

Jeepers

Thanks, mate. I was able to trace the View definition. You helped me a ton this time. – Son

Answer 2

OBJECT_DEFINITION is definitely the way to go. See the MS docs.

For example, if you want to find out the create statement for a view called dbo.example, you would do the following:

select object_definition(object_id(N'dbo.example')) as [Trigger Definition];   

That's it!

Decortication

'object_definition' is not a recognized built-in function name. using a dedicated Synapse pool. – Earpiece

I just confirmed that it still works with a Synapse Serverless SQL database. I don't have a Synapse dedicated SQL database to test against. – Decortication

Answer 3

SELECT 
CONCAT('IF OBJECT_ID(''',ss.[name],'.',o.[name],''') IS NOT NULL DROP ',CASE type WHEN 'V' THEN 'VIEW' WHEN 'IF' THEN 'FUNCTION' WHEN 'FN' THEN 'FUNCTION' WHEN 'P' THEN 'PROCEDURE' END ,' ',ss.[name],'.',o.[name],'~GO~~'
,replace(replace(replace(sm.[definition],CHAR(13), '~'),CHAR(10), '~'),'~~','~'),'~GO~')
FROM sys.objects AS o 
JOIN sys.sql_modules AS sm
    ON o.object_id = sm.object_id  
JOIN sys.schemas AS ss
    ON o.schema_id = ss.schema_id  

WHERE 1=1 
AND o.type = 'V'

1. This worked for me using SSMS in Azure Synapse for Scalar Functions(FN) , Inline Functions(IF), Procedures(P), and Views(V)
2. Using the query above in SSMS CNTL-D (Query Results to Grid)
3. copy results to a new query.
4. select a tilde character, ~ with \n (newline), CNTL-H (Replace)
5. with the find/replace popup focused use "replace using REGX", ALT+E
6. Replace All, ALT+A

It looks like you got your answer already but I struggled with formatting etc. in SSMS and Azure Data Studio so figured I'd share this.

Phyle