4

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

Ken Masters
  • 239
  • 2
  • 17

3 Answers3

6

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;
wBob
  • 13,710
  • 3
  • 20
  • 37
1

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!

daviewales
  • 2,144
  • 21
  • 30
  • `'object_definition' is not a recognized built-in function name.` using a dedicated Synapse pool. – cddt Jun 28 '23 at 00:46
  • 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. – daviewales Jun 28 '23 at 02:01
0
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.

MGoodwin
  • 1