[SQL] Data lineage - Remove the all comments from [VIEW_DEFINITION] in Azure SQL DW

I need to list the columns and tables that are used in the creation of the view [dimension].[v_dim_customer] .

To do this, I query the value of the column [VIEW_DEFINITION] which contains the SQL query for creating the view.

SELECT
v.TABLE_SCHEMA,v.TABLE_NAME, v.VIEW_DEFINITION

FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.VIEWS v
ON c.TABLE_NAME = v.TABLE_NAME
and c.TABLE_SCHEMA = v.TABLE_SCHEMA
WHERE v.table_name = 'dimension'
AND c.table_schema = 'v_dim_customer'

TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION
dimension v_dim_customer CREATE VIEW [dimension].[v_dim_customer] AS SELECT -- primary key KNA1.fk_MANDT_KUNNR AS sk_customer, -- attributes KNA1.[NAME1_nom], -- KNA1.[DEAR1_concurrents], -- KNA1.[DEAR2_responsable_adv], ----- /flag client interne/externe/----- flag_ext_int.externe_interne_code, /* flag client interne/externe niveau 2 (détaillé : intra groupe / intra société / externe)*/ flag_ext_int.externe_interne_level_2_code, -- date pour delta -- CY.max_slt_datetime -- add --18/12/2023 FROM [e3p].[KNA1] KNA1 LEFT OUTER JOIN [e3p].T005T T005T ON KNA1.[fk_MANDT_LAND1] = T005T.[fk_MANDT_LAND1] AND T005T.[bk_SPRAS_code_langue] = N'F' -- français LEFT OUTER JOIN [e3p].T016T ON KNA1.[fk_MANDT_BRSCH] = T016T.[fk_MANDT_BRSCH] -- AND KNA1.[SPRAS_code_langue] = T016T.[bk_SPRAS_code_langue] AND T016T.[bk_SPRAS_code_langue] = N'F' -- français -- modif ska 03/12 : ajout delai moyen de paiement, credit autorisé et recommendé -- LEFT OUTER JOIN [e3p].[v_KNKK_fr_compute] as KNKK ON KNA1.bk_MANDT_mandant = KNKK.bk_MANDT_mandant -- AND KNA1.bk_KUNNR_client = KNKK.bk_KUNNR_client

BUT the script contains comments that I want to exclude from my analysis. for example the column <-- KNA1.[DEAR1_concurrents]> is commented so it is not part of the columns that I need to list.

It is possible to exclude all comments but not manually because I have several other SQL queries

I tried to use regular expressions but it does not work because they are not supported in Azure SQL DW

Continue reading...