![Data lineage – Remove the all comments from [VIEW_DEFINITION] in Azure SQL DW](https://oio.lk/wp-content/themes/neeon/assets/img/noimage.jpg)
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
You need to sign in to view this answers
Leave feedback about this