Comparing schema differences between two given objects(tables/views)

DECLARE @SourcetTableOrViewName varchar(500)


DECLARE @TargetTableOrViewName varchar(500)



SET @SourcetTableOrViewName = 'SalesOrderHeader'

SET @TargetTableOrViewName = 'v_SalesOrderHeader'



IF CHARINDEX('.', @SourcetTableOrViewName) = 0

SET @SourcetTableOrViewName = 'dbo.' + @SourcetTableOrViewName



IF CHARINDEX('.', @TargetTableOrViewName) = 0

SET @TargetTableOrViewName = 'dbo.' + @TargetTableOrViewName



IF ( OBJECT_ID(@SourcetTableOrViewName) IS NOT NULL AND OBJECT_ID(@TargetTableOrViewName) IS NOT NULL )

BEGIN



;WITH SRC_CTE

AS

(SELECT

(TABLE_SCHEMA + '.' + TABLE_NAME) AS TABLENAME

,COLUMN_NAME

,COLUMN_DEFAULT

,IS_NULLABLE

,DATA_TYPE

,CASE WHEN DATA_TYPE IN ('bigint','bit','date','datetime','datetime2','datetimeoffset','float','geography','geometry'

,'hierarchyid','image','int','money','real','smalldatetime','smallint','smallmoney','sql_variant'

,'sysname','syspolicy_target_filters_type','text','time','timestamp','tinyint','uniqueidentifier','xml')

THEN 1

WHEN DATA_TYPE IN ('numeric','decimal') THEN 2

ELSE 0 -- 'binary','nchar','ntext','nvarchar','char','varbinary','varchar'

END AS TYPE_GROUP

,CHARACTER_MAXIMUM_LENGTH

,NUMERIC_PRECISION

,NUMERIC_SCALE

,COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE (TABLE_SCHEMA + '.' + TABLE_NAME) = @SourcetTableOrViewName

),TRGT_CTE

AS

(SELECT

(TABLE_SCHEMA + '.' + TABLE_NAME) AS TABLENAME

,COLUMN_NAME

,COLUMN_DEFAULT

,IS_NULLABLE

,DATA_TYPE

,CASE WHEN DATA_TYPE IN ('bigint','bit','date','datetime','datetime2','datetimeoffset','float','geography','geometry'

,'hierarchyid','image','int','money','real','smalldatetime','smallint','smallmoney','sql_variant'

,'sysname','syspolicy_target_filters_type','text','time','timestamp','tinyint','uniqueidentifier','xml')

THEN 1

WHEN DATA_TYPE IN ('numeric','decimal') THEN 2

ELSE 0 -- 'binary','nchar','ntext','nvarchar','char','varbinary','varchar'

END AS TYPE_GROUP

,CHARACTER_MAXIMUM_LENGTH

,NUMERIC_PRECISION

,NUMERIC_SCALE

,COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE (TABLE_SCHEMA + '.' + TABLE_NAME) = @TargetTableOrViewName

)

SELECT C1.TABLENAME SOURCE_TABLENAME

,C1.COLUMN_NAME SOURCE_COLUMNNAME

,CASE WHEN C1.TYPE_GROUP = 1 THEN C1.DATA_TYPE

WHEN C1.TYPE_GROUP = 2 THEN C1.DATA_TYPE + '(' + CAST(ISNULL(C1.NUMERIC_PRECISION,0) AS VARCHAR) + ',' + CAST(ISNULL(C1.NUMERIC_SCALE,0) AS VARCHAR) +')'

ELSE C1.DATA_TYPE + CASE WHEN C1.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(ISNULL(C1.CHARACTER_MAXIMUM_LENGTH,0) AS VARCHAR) + ')'

ELSE ''

END

END AS SOURCE_DATATYPE

,C1.IS_NULLABLE SOURCE_IS_NULLABLE

,C1.COLLATION_NAME SOURCE_COLLATION_NAME

,C2.TABLENAME TARGET_TABLENAME

,C2.COLUMN_NAME TARGET_COLUMNNAME

,CASE WHEN C2.TYPE_GROUP = 1 THEN C2.DATA_TYPE

WHEN C2.TYPE_GROUP = 2 THEN C2.DATA_TYPE + '(' + CAST(ISNULL(C2.NUMERIC_PRECISION,0) AS VARCHAR) + ',' + CAST(ISNULL(C2.NUMERIC_SCALE,0) AS VARCHAR) +')'

ELSE C2.DATA_TYPE + CASE WHEN C2.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(ISNULL(C2.CHARACTER_MAXIMUM_LENGTH,0) AS VARCHAR) + ')'

ELSE ''

END

END AS TARGET_DATATYPE

,C2.IS_NULLABLE TARGET_IS_NULLABLE

,C2.COLLATION_NAME TARGET_COLLATION_NAME

,CASE WHEN C1.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_SRCCOLUMN_EXIST

,CASE WHEN C2.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_TRGTCOLUMN_EXIST

INTO #TEMP

FROM SRC_CTE C1

FULL JOIN TRGT_CTE C2

ON C1.COLUMN_NAME = C2.COLUMN_NAME





SELECT *

,CASE WHEN (SOURCE_IS_NULLABLE <> TARGET_IS_NULLABLE) AND (SOURCE_COLLATION_NAME <> TARGET_COLLATION_NAME) THEN 'Collation/Null Constraint Issue'

WHEN ((SOURCE_DATATYPE <> TARGET_DATATYPE) AND(SOURCE_COLLATION_NAME <> TARGET_COLLATION_NAME)) THEN 'Data Type/Collation Issue'

WHEN (SOURCE_DATATYPE <> TARGET_DATATYPE) AND (SOURCE_IS_NULLABLE <> TARGET_IS_NULLABLE) THEN 'Data Type/Null Constraint Issue'

WHEN (SOURCE_COLLATION_NAME <> TARGET_COLLATION_NAME) THEN 'Collation Issue'

WHEN (SOURCE_DATATYPE <> TARGET_DATATYPE) THEN 'Data Type Issue'

WHEN (SOURCE_IS_NULLABLE <> TARGET_IS_NULLABLE) THEN 'NULL Constraint Issue'

WHEN IS_SRCCOLUMN_EXIST = 0 AND IS_TRGTCOLUMN_EXIST = 1 THEN 'Column Exist in Target,but not in source.'

WHEN IS_SRCCOLUMN_EXIST = 1 AND IS_TRGTCOLUMN_EXIST = 0 THEN 'Column Exist in Source,but not in target.'

ELSE 'Matched'

END AS [Difference]

FROM #TEMP

WHERE (SOURCE_DATATYPE <> TARGET_DATATYPE

OR SOURCE_IS_NULLABLE <> TARGET_IS_NULLABLE

OR SOURCE_COLLATION_NAME <> TARGET_COLLATION_NAME

OR IS_SRCCOLUMN_EXIST = 1

OR IS_TRGTCOLUMN_EXIST = 1)



IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL

DROP TABLE #TEMP



END