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
;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
No comments:
Post a Comment