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
Compare Sql Server Table Constraints
13 years ago
