GENERIC 16. Create Remaining Default constraints & missed columns

--IDENTIFY NEW CONSTRAINTS HAS BEEN CREATED IN LATER VERSION
USE [DBName1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IamTempTable]') AND type in (N'U'))
DROP TABLE [dbo].[IamTempTable]

GO

SELECT * INTO [dbo].[IamTempTable]
FROM
(
SELECT DISTINCT A.TABLE_SCHEMA,A.TABLE_NAME,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE ,
SUBSTRING((
SELECT ', ' + KCU.COLUMN_NAME
FROM [DBName1].INFORMATION_SCHEMA.TABLES AS T
JOIN [DBName1].INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.TABLE_NAME = T.TABLE_NAME
JOIN [DBName1].INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.TABLE_NAME = KCU.TABLE_NAME
WHERE T.TABLE_NAME = A.TABLE_NAME AND TC.CONSTRAINT_NAME = B.CONSTRAINT_NAME-- AND KCU.COLUMN_NAME = C.COLUMN_NAME
ORDER BY KCU.[ORDINAL_POSITION] FOR XML PATH('') ), 2, 8000) AS KEY_COLUMNS
FROM [DBName1].INFORMATION_SCHEMA.TABLES A
JOIN [DBName1].INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.TABLE_NAME = B.TABLE_NAME
JOIN [DBName1].INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
ON A.TABLE_NAME = B.TABLE_NAME AND C.CONSTRAINT_NAME =B.CONSTRAINT_NAME
EXCEPT
SELECT DISTINCT A.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS ,A.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS ,B.CONSTRAINT_NAME COLLATE SQL_Latin1_General_CP1_CI_AS ,B.CONSTRAINT_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS ,
SUBSTRING((
SELECT ', ' + KCU.COLUMN_NAME
FROM [DBName2].INFORMATION_SCHEMA.TABLES AS T
JOIN [DBName2].INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.TABLE_NAME = T.TABLE_NAME
JOIN [DBName2].INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.TABLE_NAME = KCU.TABLE_NAME
WHERE T.TABLE_NAME = A.TABLE_NAME AND TC.CONSTRAINT_NAME = B.CONSTRAINT_NAME-- AND KCU.COLUMN_NAME = C.COLUMN_NAME
ORDER BY KCU.[ORDINAL_POSITION] FOR XML PATH('') ), 2, 8000) COLLATE SQL_Latin1_General_CP1_CI_AS AS KEY_COLUMNS
FROM [DBName2].INFORMATION_SCHEMA.TABLES A
JOIN [DBName2].INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.TABLE_NAME = B.TABLE_NAME
JOIN [DBName2].INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
ON A.TABLE_NAME = B.TABLE_NAME AND C.CONSTRAINT_NAME =B.CONSTRAINT_NAME
) A

GO

SELECT 'USE [DBName2]'
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '-- CREATE CONSTRAINTS WHICH ARE THERE IN LATER VERSION'
UNION ALL
SELECT
'IF OBJECT_ID(N'''+A.DEFAULT_CONSTRAINT_NAME+''') IS NULL ALTER TABLE ['+A.TABLE_SCHEMA+'].['+A.TABLE_NAME+'] ADD CONSTRAINT ['+A.DEFAULT_CONSTRAINT_NAME+'] DEFAULT '+ A.DEFINITION + ' FOR '+ A.COLUMN_NAME + ' ' + ' GO'
FROM
(
SELECT SDC.NAME AS DEFAULT_CONSTRAINT_NAME,SDC.DEFINITION , AC.NAME AS COLUMN_NAME, T.NAME AS TABLE_NAME,S.name AS TABLE_SCHEMA
FROM [DBName1].SYS.TABLES T
JOIN [DBName1].SYS.schemas S ON T.schema_id = S.schema_id
JOIN [DBName1].SYS.ALL_OBJECTS AO on AO.name = T.name
join [DBName1].SYS.ALL_COLUMNS AC on AO.object_id = AC.object_id
JOIN [DBName1].SYS.DEFAULT_CONSTRAINTS SDC ON SDC.parent_OBJECT_ID = AC.object_id AND SDC.parent_column_id = AC.column_id
--AND SDC.object_id = AO.object_id
EXCEPT
SELECT SDC.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS DEFAULT_CONSTRAINT_NAME,SDC.DEFINITION COLLATE SQL_Latin1_General_CP1_CI_AS , AC.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS COLUMN_NAME, T.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS TABLE_NAME,S.name COLLATE SQL_Latin1_General_CP1_CI_AS AS TABLE_SCHEMA
FROM [DBName2].SYS.TABLES T
JOIN [DBName2].SYS.schemas S ON T.schema_id = S.schema_id
JOIN [DBName2].SYS.ALL_OBJECTS AO on AO.name = T.name AND AO.schema_id = T.schema_id
join [DBName2].SYS.ALL_COLUMNS AC on AO.object_id = AC.object_id
JOIN [DBName2].SYS.DEFAULT_CONSTRAINTS SDC ON SDC.parent_OBJECT_ID = AC.object_id AND SDC.parent_column_id = AC.column_id
--AND SDC.object_id = AO.object_id
) A
UNION ALL
SELECT 'GO'
UNION ALL
SELECT '-- DROP CONSTRAINTS WHICH ARE NOT THERE IN LATER VERSION'
UNION ALL
SELECT --DISTINCT T.TABLE_NAME,B.CONSTRAINT_NAME,B.CONSTRAINT_TYPE
DISTINCT
'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'''+T.TABLE_NAME+''') AND name = N'''+B.CONSTRAINT_NAME+''') ALTER TABLE ['+T.TABLE_SCHEMA+'].['+T.TABLE_NAME+'] DROP CONSTRAINT ['+B.CONSTRAINT_NAME+']'+ ' GO'
FROM [dbo].[IamTempTable] T
JOIN [DBName2].INFORMATION_SCHEMA.TABLES A ON A.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = T.TABLE_NAME AND A.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = T.TABLE_SCHEMA
JOIN [DBName2].INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
WHERE B.TABLE_SCHEMA COLLATE SQL_Latin1_General_CP1_CI_AS = T.TABLE_SCHEMA AND B.TABLE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = T.TABLE_NAME AND B.CONSTRAINT_TYPE COLLATE SQL_Latin1_General_CP1_CI_AS = T.CONSTRAINT_TYPE
UNION ALL
SELECT 'GO'
--UNION ALL
--SELECT '--THIS SQL STATEMENT EXPLICITLY FOR  DATA MIGRATION'
--UNION ALL
--SELECT 'WITH CTE_Dup AS ( SELECT *, ROW_NUMBER()OVER ( PARTITION BY BoardId, ValidatorFunctionId, [Parameters] ORDER BY SBoardId, ValidatorFunctionId, [Parameters]) AS ROW_NO FROM [Config]) DELETE FROM CTE_Dup WHERE ROW_NO > 1; '
UNION ALL
--SELECT 'GO'
--UNION ALL
SELECT '-- CREATE NEW CONSTRAINTS FROM LATER VERSION'
UNION ALL
SELECT --DISTINCT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,KEY_COLUMNS
DISTINCT
'IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_NAME(OBJECT_ID) = N'''+TABLE_NAME +''' AND name = N'''+CONSTRAINT_NAME+''')'+
CASE WHEN CONSTRAINT_TYPE IN ('PRIMARY KEY') THEN 'ALTER TABLE ['+TABLE_SCHEMA+'].['+ TABLE_NAME +'] DROP CONSTRAINT ['+CONSTRAINT_NAME +'] '
ELSE 'DROP INDEX ['+CONSTRAINT_NAME+'] ON ['+TABLE_SCHEMA+'].['+ TABLE_NAME +'] WITH ( ONLINE = OFF ) ' END+
'GO ;WITH CTE_Dup AS ( SELECT *, ROW_NUMBER()OVER ( PARTITION BY ' + + KEY_COLUMNS + ' ORDER BY ' + KEY_COLUMNS + ' ) AS ROW_NO FROM ['+TABLE_SCHEMA+'].['+ TABLE_NAME +'] ) DELETE FROM CTE_Dup WHERE ROW_NO > 1; '
+' IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = '''+CONSTRAINT_NAME+''' AND TABLE_NAME = '''+TABLE_NAME+''' AND TABLE_SCHEMA = '''+TABLE_SCHEMA +''') ALTER TABLE ['+TABLE_SCHEMA+'].['+ TABLE_NAME +'] ADD CONSTRAINT ['+ CONSTRAINT_NAME+'] '+ CASE WHEN CONSTRAINT_TYPE='PRIMARY KEY' THEN 'PRIMARY KEY CLUSTERED ' WHEN CONSTRAINT_TYPE='UNIQUE' THEN 'UNIQUE NONCLUSTERED ' END+' ( '+ KEY_COLUMNS +
')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO'
FROM IamTempTable T
UNION ALL
SELECT 'GO '

GO
--IDENTIFY NEW CONSTRAINTS HAS BEEN CREATED IN LATER VERSIONUSE [DBName1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IamTempTable]') AND type in (N'U'))
DROP TABLE [dbo].[IamTempTable]

GO