Finding Column Definitions in Oracle, SQL Server and DB2

In order to speed up the development of Azure Data Factory pipelines, I created a table containing definitions of various databases tables. Each of the big three databases provide a method for querying table and column definitions.

SQL Server

select 
     table_catalog, 
     table_schema, 
     table_name,
     column_name,
     ordinal_position,
     data_type,
     coalesce(character_maximum_length,numeric_precision) as column_length 
from information_schema.columns

Oracle

select 
     'myOracleDatabase' as databasename, 
     owner, 
     table_name, 
     column_name, 
     column_id, 
     data_type, 
     data_length 
from all_tab_columns

DB2

select 
     'myDB2Database' as databasename, 
     tabschema, 
     tabname, 
     colname, 
     colno, 
     typename, 
     length 
from syscat.columns

The following query makes use of a pipeline source table to populate Azure Data Lake Store and ensure that dates are formatted correctly and removes extra CR/LF characters.

DECLARE @schemaTables VARCHAR(8000)= 'APPS.AP_INVOICES_ALL, APPS.AP_INVOICE_PAYMENTS_ALL, APPS.AP_INVOICE_LINES_ALL'; -- ex. ABL.ABL_WQ_STATUS
BEGIN
    WHILE LEN(@schemaTables) > 0
        BEGIN
            SELECT 'select '+SUBSTRING(
(
    SELECT ', '+CASE
                    WHEN dataType LIKE '%char%'
                    THEN CASE
                             WHEN srcType = 'SQL'
                             THEN 'REPLACE(REPLACE('+columnName+', CHAR(13),'' ''), CHAR(10),'' '')'
                             WHEN srcType = 'ORACLE'
                             THEN 'TRANSLATE('+columnName+', CHR(13)||CHR(10),'' '')'
                             WHEN srcType = 'DB2'
                             THEN 'REPLACE(REPLACE('+columnName+', CHR(13),'' ''), CHR(10),'' '')'
                             ELSE columnName
                         END
                    WHEN dataType LIKE '%date%'
                    THEN CASE
                             WHEN srcType = 'ORACLE'
                             THEN 'TO_CHAR('+columnName+', ''YYYY-MM-DD HH24:MI:SS'')'
                             WHEN srcType = 'DB2'
                             THEN 'CHAR('+columnName+')'
                             ELSE columnName
                         END
                    ELSE columnName
                END
    FROM ADMIN.pipelineSourceTables
    WHERE schemaName+'.'+tableName = trim(LEFT(@schemaTables, CHARINDEX(',', @schemaTables+',')-1))
    ORDER BY POSITION FOR XML PATH('')
), 2, 200000)+' from '+LEFT(@schemaTables, CHARINDEX(',', @schemaTables+',')-1);
            SET @schemaTables = STUFF(@schemaTables, 1, CHARINDEX(',', @schemaTables+','), '');
        END;
END;

Sample Output

select 
     INVOICE_ID, 
     TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS'), 
     LAST_UPDATED_BY, 
     VENDOR_ID, 
     TRANSLATE(INVOICE_NUM, CHR(13)||CHR(10),' '), 
     SET_OF_BOOKS_ID, 
     TRANSLATE(INVOICE_CURRENCY_CODE, CHR(13)||CHR(10),' '), 
     TRANSLATE(PAYMENT_CURRENCY_CODE, CHR(13)||CHR(10),' '), 
     PAYMENT_CROSS_RATE
from AP.AP_INVOICES_ALL