Compare Sql Server Table Constraints

Hello friends, after a long period of time I’m back into my blog. As due to continuous projects didn’t get a long stretch to sit with my blog. 

In a BI project I faced a lot challenges for migrating some huge databases which ended up with lot of dependencies on sql server schema and object comparison tools(Redgate Sql Data compare,Sql compare,Xsql) to make my SSIS to execute smoothly. Now I’m in a workshop for synchronizing two databases. In the middle of that I’m sharing some useful sql queries which will be useful for happy coding.

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GET_ALL_CONSTRAINTS]') 
and objectproperty(id, N'isprocedure') = 1)
DROP PROCEDURE [dbo].[SP_GET_ALL_CONSTRAINTS]
go
CREATE PROC SP_GET_ALL_CONSTRAINTS
(
	@sourceServer nvarchar(1000),
	@targetServer nvarchar(1000)
)
--@targetServer db to be migrated(old db) eg 12.3
--@sourceServer is the new database having all new schemas scripts   eg:12.4
as
SELECT	
	'NEW'				AS CHANGE,
	DatabaseName		AS DATABASENAME,
	TABLE_NAME			AS TABLENAME,
	COLUMN_NAME			AS COLUMNNAME,
	CONSTRAINT_NAME		AS CONSTRAINTNAME,
	'N-A'				AS NEW_CONSTRAINTNAME,
	CONSTRAINT_TYPE		AS CONSTRAINTTYPE, 
	ORDINAL_POSITION	AS ORDINALPOSITION,
	REF_PK_KEY			AS REFERENCEPKKEY,
	ParentTable			AS ParentTable,
	ParentField			AS ParentField,
	TYPE_CLUSTER		AS TYPECLUSTER,
	CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT' + ' ' + CONSTRAINT_NAME + ' PRIMARY KEY (' + COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT '+ CONSTRAINT_NAME + ' ' + 
						CONSTRAINT_TYPE +  '(' + REF_PK_KEY + ') REFERENCES ' + ParentTable + '(' + ParentField + ')' 
			ELSE 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME  + ')'
			END AS Script
FROM
	tbl_ALL_CONSTRAINTS
WHERE
	DatabASeName = @targetServer
AND
	CONSTRAINT_NAME NOT IN
	(
		SELECT 	CONSTRAINT_NAME
		FROM 	tbl_ALL_CONSTRAINTS
		WHERE	DatabaseName = @sourceServer
		)
UNION ALL
-- select the changed columns
SELECT	
	'MODIFIED'			AS CHANGE,
	v1.DatabaseName		AS DATABASENAME,
	v1.TABLE_NAME		AS TABLENAME,
	v1.COLUMN_NAME		AS COLUMNNAME,
	v1.CONSTRAINT_NAME	AS CONSTRAINTNAME,
	v2.CONSTRAINT_NAME	AS NEW_CONSTRAINTNAME,
	v1.CONSTRAINT_TYPE	AS CONSTRAINTTYPE, 
	v1.ORDINAL_POSITION AS ORDINALPOSITION,
	v1.REF_PK_KEY		AS REFERENCEPKKEY,
	v1.ParentTable		AS ParentTable,
	v1.ParentField		AS ParentField,
	v1.TYPE_CLUSTER		AS TYPECLUSTER,
	CASE v1.CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME  + ' ALTER TABLE dbo.' + v1.TABLE_NAME 
					+ ' ADD CONSTRAINT' + ' ' + v2.CONSTRAINT_NAME 
					+ ' PRIMARY KEY (' + v1.COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT ' + v1.CONSTRAINT_NAME  +  ' ALTER TABLE dbo.' + v1.TABLE_NAME 
						+ ' ADD CONSTRAINT '+ v2.CONSTRAINT_NAME + ' ' + v1.CONSTRAINT_TYPE  + ' (' + v1.COLUMN_NAME + ') REFERENCES ' 
						+ v1.ParentTable + '(' + v1.ParentField + ')'
			ELSE 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME + ' ALTER TABLE dbo.' + v1.TABLE_NAME + ' ADD CONSTRAINT ' + v1.CONSTRAINT_NAME + ' UNIQUE (' + v1.COLUMN_NAME  + ')'
			END AS Script
FROM 	
	tbl_ALL_CONSTRAINTS v1
JOIN
	tbl_ALL_CONSTRAINTS v2
ON
	v1.TABLE_NAME  = v2.TABLE_NAME
AND 
	v1.COLUMN_NAME=v2.COLUMN_NAME
AND
	v1.CONSTRAINT_TYPE=v2.CONSTRAINT_TYPE
AND	
	v1.DatabaseName = @sourceServer
AND	
	v2.DatabaseName = @targetServer
AND 
	v1.CONSTRAINT_NAME<>v2.CONSTRAINT_NAME
GO
SET ANSI_NULLS ON 
GO
	

The below script will populate all the sql server metadata information

----QUERY to Find out All the Constraints in Detailed
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateAllConstraintsInTable]') and objectproperty(id, N'isprocedure') = 1)
drop procedure [dbo].[sp_CreateAllConstraintsInTable]
go
CREATE PROCEDURE sp_CreateAllConstraintsInTable 
@sourceServer  nvarchar(30), --Database to be compared (New Database)  
@targetServer  nvarchar(30)  --Database to be compared with(Old Database)  
as 
declare @sql varchar(max)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_ALL_CONSTRAINTS]') and objectproperty(id, N'istable') = 1)
drop table [dbo].[tbl_ALL_CONSTRAINTS]

select @sql=

'	SELECT 
	u.TABLESCHEMA,
	u.DatabaseName,
	u.TABLE_NAME,
	u.COLUMN_NAME,
	u.CONSTRAINT_NAME,
	u.CONSTRAINT_TYPE,
	u.ORDINAL_POSITION,
	u.REF_PK_KEY,
	u.ParentTable,
	u.ParentField,
	u.TYPE_CLUSTER into tbl_ALL_CONSTRAINTS
	FROM 
	(
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM '+
			@sourceServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN  '+@sourceServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
			)
			union all
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM  
			 '+@targetServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN '+@targetServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
		)
	) u
	ORDER BY 
		u.TABLESCHEMA, u.CONSTRAINT_TYPE DESC, 
		u.TABLE_NAME,u.ORDINAL_POSITION'
exec (@sql)
go

Get this function also from here