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