Introduction
All the DB
Developer are like to use DB Diagram to design Database. They used drag and
drop process to make the Foreign key references between the table. Quite easy…
is it?
But the real
problem is that when we are trying to generate the foreign key definition (Script)
to deploy it in the Production DB. There is no shortcut method to get the definition
of foreign key in entire DB. We can list the entire foreign key in a DB but not
get the definition all together.
In this
article I we are trying to create a Script which can generate the definition of
the entire foreign key in the Database.
Hope you will
find it informative.
To list all the Foreign key
Constraint in Database
SELECT RC.CONSTRAINT_NAME FK_Name, KF.TABLE_SCHEMA FK_Schema,
KF.TABLE_NAME FK_Table, KF.COLUMN_NAME FK_Column,
RC.UNIQUE_CONSTRAINT_NAME PK_Name, KP.TABLE_SCHEMA PK_Schema,
KP.TABLE_NAME PK_Table, KP.COLUMN_NAME PK_Column,
RC.MATCH_OPTION MatchOption, RC.UPDATE_RULE UpdateRule,
RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF
ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP
ON RC.UNIQUE_CONSTRAINT_NAME =
KP.CONSTRAINT_NAME;
By this
script we can list all the Foreign key and their related table objects.
Create Script
Now we are
creating a Stored Procedure to generate the Definition of Foreign key
constraint
BEGIN
DECLARE @v_FK_Name VARCHAR(Max),
@v_FK_Schema VARCHAR(Max),
@v_FK_Table VARCHAR(Max),
@v_FK_Column VARCHAR(Max),
@v_PK_Name VARCHAR(Max),
@v_PK_Schema VARCHAR(Max),
@v_PK_Table VARCHAR(Max),
@v_PK_Column VARCHAR(Max),
@v_MatchOption VARCHAR(Max),
@v_UpdateRule VARCHAR(Max),
@v_DeleteRule VARCHAR(Max);
DECLARE @v_TotalRecords BIGINT,
@v_StartRecords BIGINT;
DECLARE @v_SQLScript VARCHAR(Max);
IF OBJECT_ID('tempdb..#temp_FKDefination')IS NOT NULL
DROP TABLE #temp_FKDefination;
CREATE TABLE #temp_FKDefination
(
Defination VARCHAR(Max)
);
IF OBJECT_ID('tempdb..#tbl_FK')IS NOT NULL
DROP TABLE #tbl_FK;
SELECT ROW_NUMBER() OVER(ORDER BY RC.CONSTRAINT_NAME) AS SrlNo,
RC.CONSTRAINT_NAME FK_Name, KF.TABLE_SCHEMA FK_Schema,
KF.TABLE_NAME FK_Table, KF.COLUMN_NAME FK_Column,
RC.UNIQUE_CONSTRAINT_NAME
PK_Name, KP.TABLE_SCHEMA PK_Schema,
KP.TABLE_NAME PK_Table, KP.COLUMN_NAME PK_Column,
RC.MATCH_OPTION MatchOption, RC.UPDATE_RULE UpdateRule,
RC.DELETE_RULE DeleteRule
INTO #tbl_FK
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF
ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP
ON RC.UNIQUE_CONSTRAINT_NAME
= KP.CONSTRAINT_NAME;
SET @v_TotalRecords = (SELECT COUNT(*) FROM #tbl_FK);
SET @v_StartRecords
= 1;
WHILE @v_StartRecords
<= @v_TotalRecords
BEGIN
SELECT @v_FK_Name = FK_Name,
@v_FK_Schema
= FK_Schema,
@v_FK_Table
= FK_Table,
@v_FK_Column
= FK_Column,
@v_PK_Name
= PK_Name,
@v_PK_Schema
= PK_Schema,
@v_PK_Table
= PK_Table,
@v_PK_Column
= PK_Column,
@v_MatchOption
= MatchOption,
@v_UpdateRule
= UpdateRule,
@v_DeleteRule
= DeleteRule
FROM #tbl_FK
WHERE SrlNo = @v_StartRecords;
SET @v_SQLScript = '';
SET @v_SQLScript = 'ALTER TABLE ['+ @v_FK_Schema +'].[' + @v_FK_Table +']';
SET @v_SQLScript
= @v_SQLScript + ' WITH NOCHECK ADD CONSTRAINT ['+
@v_FK_Name +']';
SET @v_SQLScript = @v_SQLScript + ' FOREIGN KEY(['+ @v_FK_Column +'])';
SET @v_SQLScript = @v_SQLScript + ' REFERENCES ['+ @v_PK_Schema +'].[' + @v_PK_Table +']';
SET @v_SQLScript = @v_SQLScript + ' (['+ @v_PK_Column +'])';
INSERT INTO
#temp_FKDefination (Defination)
VALUES ( @v_SQLScript );
SET @v_SQLScript = 'ALTER TABLE ['+ @v_FK_Schema +'].[' + @v_FK_Table +']';
SET @v_SQLScript = @v_SQLScript + ' CHECK CONSTRAINT ['+ @v_FK_Name +']'
INSERT INTO #temp_FKDefination (Defination)
VALUES ( @v_SQLScript );
SET @v_StartRecords = @v_StartRecords + 1;
END
SELECT * FROM #temp_FKDefination;
END
GO
Output is:
Analyze the Output
If we analyze
the output we find
ALTER TABLE [dbo].[CancerHistory] WITH NOCHECK
ADD CONSTRAINT [FK_CancerHistory_MasterSystemSiteOfRadiationTherapy]
FOREIGN KEY([idSiteOfRadiationTherapy])
REFERENCES [dbo].[MasterSystemSiteOfRadiationTherapy] ([idSiteOfRadiationTherapy]);
ALTER TABLE [dbo].[CancerHistory]
CHECK CONSTRAINT [FK_CancerHistory_MasterSystemSiteOfRadiationTherapy];
Hope you like
it.
Posted By: JOYDEEP DAS
