Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Created November 29, 2013 18:38
Show Gist options
  • Save lionofdezert/7710127 to your computer and use it in GitHub Desktop.
Save lionofdezert/7710127 to your computer and use it in GitHub Desktop.
Stored Procedure, to create missing foreign keys at target database after comparing both source and target databases.
--DROP IF ALREADY CREATED
IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateForignKeys')
DROP PROC [dbo].[SYNC_CreateForignKeys]
GO
--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Stored Procedure, to create missing foreign keys at target database after comparing
both source and target databases.
EXEC [dbo].[SYNC_CreateForignKeys] 'AdventureWork_StableVersion','AventureWork_Target'
*/
CREATE PROCEDURE [dbo].[SYNC_CreateForignKeys]
(
@SyncFromDB VARCHAR(200), --Source database name
@SyncToDB VARCHAR(200) --Target database name
)
AS
BEGIN
CREATE TABLE #AlterStatements (TSQLStmt VARCHAR(MAX))
EXEC('
INSERT INTO #AlterStatements
SELECT ''IF NOT EXISTS(SELECT 1 FROM '+@SyncToDB+'.SYS.OBJECTS WHERE NAME = ''''''+Constraint_name+'''''') BEGIN ALTER TABLE '+@SyncToDB+'.['' +Referencing_Schema_name+''].[''+Referencing_Object_name+''] WITH CHECK ADD CONSTRAINT [''+Constraint_name+''] FOREIGN KEY( [''+Referencing_Column_Name
+'']) REFERENCES [''+Referenced_Schema_name+''].[''+Referenced_Object_name+''] ([''+Referenced_Column_Name+''])
ALTER TABLE '+@SyncToDB+'.[''+Referencing_Schema_name+''].[''+Referencing_Object_name+ ''] CHECK CONSTRAINT [''+Constraint_name +''] END
ELSE
BEGIN
ALTER TABLE '+@SyncToDB+'.['' +Referencing_Schema_name+''].[''+Referencing_Object_name+''] WITH CHECK ADD CONSTRAINT [''+Constraint_name+''_1] FOREIGN KEY( [''+Referencing_Column_Name
+'']) REFERENCES [''+Referenced_Schema_name+''].[''+Referenced_Object_name+''] ([''+Referenced_Column_Name+''])
ALTER TABLE '+@SyncToDB+'.[''+Referencing_Schema_name+''].[''+Referencing_Object_name+ ''] CHECK CONSTRAINT [''+Constraint_name +''_1] END
''
FROM
(
SELECT
S1.NAME as Referencing_Schema_name
,o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
,S2.NAME as Referenced_Schema_name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from '+@SyncFromDB+'..sysforeignkeys fk
inner join '+@SyncFromDB+'..sysobjects o1 on fk.fkeyid = o1.id
inner join '+@SyncFromDB+'..sysobjects o2 on fk.rkeyid = o2.id
inner join '+@SyncFromDB+'..syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join '+@SyncFromDB+'..syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join '+@SyncFromDB+'..sysobjects s on fk.constid = s.id
inner join '+@SyncFromDB+'.sys.tables t1 on t1.object_id=c1.id
inner join '+@SyncFromDB+'.sys.tables t2 on t2.object_id=c2.id
INNER JOIN '+@SyncFromDB+'.SYS.schemas S1 ON T1.SCHEMA_ID=S1.SCHEMA_ID
INNER JOIN '+@SyncFromDB+'.SYS.schemas S2 ON T2.SCHEMA_ID=S2.SCHEMA_ID
)MASTERDB
WHERE NOT EXISTS
(
SELECT * FROM
(SELECT
S1.NAME as Referencing_Schema_name
,o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
,S2.NAME as Referenced_Schema_name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from '+@SyncToDB+'..sysforeignkeys fk
inner join '+@SyncToDB+'..sysobjects o1 on fk.fkeyid = o1.id
inner join '+@SyncToDB+'..sysobjects o2 on fk.rkeyid = o2.id
inner join '+@SyncToDB+'..syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join '+@SyncToDB+'..syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join '+@SyncToDB+'..sysobjects s on fk.constid = s.id
inner join '+@SyncToDB+'.sys.tables t1 on t1.object_id=c1.id
inner join '+@SyncToDB+'.sys.tables t2 on t2.object_id=c2.id
INNER JOIN '+@SyncToDB+'.SYS.schemas S1 ON T1.SCHEMA_ID=S1.SCHEMA_ID
INNER JOIN '+@SyncToDB+'.SYS.schemas S2 ON T2.SCHEMA_ID=S2.SCHEMA_ID
) SlaveDB
WHERE SlaveDB.Referencing_Schema_name=MASTERDB.Referencing_Schema_name
AND SlaveDB.Referencing_Object_name=MASTERDB.Referencing_Object_name
AND SlaveDB.referencing_column_Name=MASTERDB.referencing_column_Name
AND SlaveDB.Referenced_Schema_name=MASTERDB.Referenced_Schema_name
AND SlaveDB.Referenced_Object_name=MASTERDB.Referenced_Object_name
-- AND Constraint_name=Constraint_name
)
ORDER BY MASTERDB.Referencing_Object_name
')
----CURSOR TO EXECUTE/PRINT EACH STATEMENT SEPARATELY-----
DECLARE MY_CURSOR CURSOR READ_ONLY
FOR
SELECT TSQLStmt FROM #AlterStatements
OPEN My_Cursor
DECLARE @SQLstmt VARCHAR(MAX)
Fetch NEXT FROM MY_Cursor INTO @SQLstmt
While (@@FETCH_STATUS <> -1)
BEGIN
EXEC (@SQLstmt)
-- PRINT (@SQLstmt)
FETCH NEXT FROM MY_CURSOR INTO @SQLstmt
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
-----
DROP TABLE #AlterStatements
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment