USP_AUDITHELP_GET_SOURCE_VS_AUDIT_COLUMNS

CREATE procedure [dbo].[USP_AUDITHELP_GET_SOURCE_VS_AUDIT_COLUMNS] (@SOURCETABLENAME  nvarchar(128))
as
/*
gets the fields in the source table and the matching columns in the audit table.
Used to make sure the fields in the source exist exactly on the audit table
*/

declare @auditTableName nvarchar(128)
set @auditTableName = @SOURCETABLENAME + 'AUDIT'

select 
    SOURCE.COLUMN_NAME as SOURCECOLUMNNAME, 
    coalesce(AUDIT.COLUMN_NAME, '') as AUDITCOLUMNNAME,
    SOURCE.DATA_TYPE as SOURCEDATATYPE,
    coalesce(AUDIT.DATA_TYPE, '') as AUDITDATATYPE, 
    coalesce(SOURCE.CHARACTER_MAXIMUM_LENGTH, 0) as SOURCECHARACTERMAXIMUMLENGTH, 
    coalesce(AUDIT.CHARACTER_MAXIMUM_LENGTH, 0) as AUDITCHARACTERMAXIMUMLENGTH,
    coalesce(SOURCE.DOMAIN_NAME, '') as SOURCEDOMAINNAME, 
    coalesce(AUDIT.DOMAIN_NAME, '') as AUDITDOMAINNAME,
    coalesce(SOURCE.NUMERIC_PRECISION, 0) as SOURCEPRECISION,
    coalesce(SOURCE.NUMERIC_SCALE, 0) as SOURCESCALE,
    coalesce(AUDIT.NUMERIC_PRECISION, 0) as AUDITPRECISION,
    coalesce(AUDIT.NUMERIC_SCALE, 0) as AUDITSCALE

from
    (select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DOMAIN_NAME, NUMERIC_PRECISION, NUMERIC_SCALE
    from INFORMATION_SCHEMA.COLUMNS 
    where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @SOURCETABLENAME) and (COLUMN_NAME not in ('ID', 'TS', 'TSLONG')) 
    and (COLUMNPROPERTY(OBJECT_ID('[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') = 0) 
    ) as SOURCE 

    left outer join 

    (select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, DOMAIN_NAME, NUMERIC_PRECISION, NUMERIC_SCALE
    from INFORMATION_SCHEMA.COLUMNS 
    where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @AUDITTABLENAME)
    ) as AUDIT 

on SOURCE.COLUMN_NAME = AUDIT.COLUMN_NAME;

return 0;