Liste de champs des tables

Créer le même code à adapter à chaque table selon les champs et le type.

Cela peut être une activité assez plate. On peut créer un code qui crée notre code. Peut-on appeler une métacreation, ou métacodification, mais, peut-être, il n'existe pas ce mot en français.

Alors, ce SQL script nous liste les champs de tables, et après, c'est une autre histoire.

BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
SELECT
     TABLE_SCHEMA
    ,TABLE_NAME
    ,ORDINAL_POSITION AS rownumber
    ,COLUMN_NAME AS 'ColumnName'
    ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
    ,IS_NULLABLE
INTO #TMP
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaSource AND TABLE_NAME = @TableSource;

Utilisation

DECLARE @TABLE_NAME as NVARCHAR(100);
DECLARE @TABLE_SCHEMA as NVARCHAR(100);
DECLARE @MyElement as NVARCHAR(100);
DECLARE @MyFieldXml as NVARCHAR(100);
SET @TABLE_NAME = 'Customer'
SET @TABLE_SCHEMA = 'dbo'
--MyFieldXml
SET @MyElement  = 'msg_' + @TABLE_NAME; -- we must check if this name is ok.
SET @MyFieldXml = 'Message';  -- we must check if this name is ok.

SET NOCOUNT ON

BEGIN TRY DROP TABLE #TMP END TRY BEGIN CATCH END CATCH;
SELECT
     TABLE_SCHEMA
    ,TABLE_NAME
    ,ORDINAL_POSITION AS rownumber
    ,COLUMN_NAME AS 'ColumnName'
    ,DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END AS 'DataType'
    ,IS_NULLABLE
    ,'@'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END as procedureParameter
    , 'x.m.value(''('+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV1
    , '['+@MyFieldXml+'].value(''('+@MyElement+'/'+TABLE_NAME+'/@'+COLUMN_NAME+')[1]'', '''+DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END+''') as ['+COLUMN_NAME+']' as xmlParameterV2
    , 'DECLARE @'+REPLACE(COLUMN_NAME,' ','_') + ' AS ' + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + ';' AS [DeclareVariable]
    , '['+COLUMN_NAME+'] ' + + DATA_TYPE + CASE WHEN CHARacter_maximum_length IS NOT NULL THEN '(' + CAST(CHARacter_maximum_length AS NVARCHAR(100)) + ')' ELSE '' END + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END  AS CreateField
INTO #TMP
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME;


DECLARE @SQL AS NVARCHAR(MAX);
SET @SQL = '';

SELECT @SQL = @SQL
        + CASE WHEN @SQL = '' THEN '  ' ELSE ', ' END
        + xmlParameterV2
        + CHAR(13) + CHAR(10)
FROM
#TMP;

SET @SQL = 'SELECT'+ CHAR(13) + CHAR(10)
          + @SQL
          + 'FROM   ['+@TABLE_NAME+']'+ CHAR(13) + CHAR(10)
          + 'WHERE  ['+@MyFieldXml+'].exist(''/'+@MyElement+'/'+@TABLE_NAME+''') = 1'


PRINT @SQL

Resultat

SELECT
  [Message].value('(msg_Customer/Customer/@CustomerId)[1]', 'int') as [CustomerId]
, [Message].value('(msg_Customer/Customer/@Timestamp)[1]', 'timestamp') as [Timestamp]
, [Message].value('(msg_Customer/Customer/@CustomerNumber)[1]', 'int') as [CustomerNumber]
, [Message].value('(msg_Customer/Customer/@CustomerName)[1]', 'nvarchar(100)') as [CustomerName]
FROM   [Customer]
WHERE  [Message].exist('/msg_Customer/Customer') = 1