Wednesday, July 3, 2013

Generate Foreign Key scripts of selected database

SET NOCOUNT ON

      ;WITH CTE
      AS(
SELECT  RC.CONSTRAINT_NAME FK_NAME
, KF.TABLE_NAME FK_TABLE
, KF.COLUMN_NAME FK_COLUMN
, RC.UNIQUE_CONSTRAINT_NAME PK_NAME
, KP.TABLE_NAME PK_TABLE
, KP.COLUMN_NAME PK_COLUMN
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
)
SELECT DISTINCT 'IF NOT EXISTS(SELECT TOP 1 1 FROM  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ' + CHAR(10) + 
'WHERE CONSTRAINT_NAME=''' + FK_NAME +''')' + CHAR(10) +
'BEGIN' + CHAR(10) + CHAR(9) +
'ALTER TABLE ' + FK_TABLE +  CHAR(10) + CHAR(9) +
'ADD CONSTRAINT '+ FK_NAME + ' FOREIGN KEY (' + 
       STUFF( (SELECT   ','+FK_COLUMN
               FROM CTE P2
               WHERE P2.FK_NAME = P1.FK_NAME
  AND   P2.FK_TABLE = P1.FK_TABLE
  GROUP BY FK_COLUMN
  ORDER BY FK_COLUMN
               FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') 
  + ')' +  CHAR(10) + CHAR(9) + 
'REFERENCES ' + PK_TABLE +'('+ 
STUFF( (SELECT   ','+PK_COLUMN
               FROM CTE P3
               WHERE P3.PK_TABLE = P1.PK_TABLE
  AND   P3.PK_NAME = P1.PK_NAME
  GROUP BY PK_COLUMN
  ORDER BY PK_COLUMN
  FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') ,1,1,'')   + ')' +  CHAR(10) +
'END' + CHAR(10) + 
'ELSE' + CHAR(10) + 
'BEGIN' + CHAR(10) + CHAR(9) +
'SELECT ''' + FK_NAME + ' ALREADY EXISTS.''' + CHAR(10) + 
'END' + CHAR(10) + CHAR(10)
FROM CTE P1;

SET NOCOUNT OFF


Result :-


No comments:

Post a Comment