Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 09/15/2008 at 08:36 PM	SQLServer\Code

This code gets all foreign key constraints of a database and creates the appropriate drop or create statements.
It is also possible, to restrict the statements to

• a defined PrimaryKey table, which means we get all tables which are depending on this table
• a defined ForeignKey table, which means we get all constraints of this particular table


Code section
Code section

DECLARE @Mode INT
, @FK_Searched_Table sysname
, @PK_Searched_Table sysname

set @MODE = 1
SET @FK_Searched_Table = '%'
SET @PK_Searched_Table = 'WfProcesses'

DECLARE  @ConstraintName sysname
,@FK_TABLE sysname
,@PK_TABLE sysname
,@FK_Column sysname
,@PK_Column sysname
,@DeleteAction INT
,@DeleteActionDesc sysname
,@UpdateAction INT
,@UpdateActionDesc sysname
,@LF CHAR(2)

DECLARE TestCursor CURSOR FOR 
SELECT
a.name [ConstraintName]
,b.NAME [FK-Table]
,(SELECT [name] FROM [sys].tables WHERE OBJECT_ID = c.referenced_object_id) [PK-Table]
,(SELECT [name] FROM sys.columns WHERE OBJECT_ID = b.object_id AND column_id = c.parent_column_id) [FK-Column]
,(SELECT [name] FROM sys.columns WHERE OBJECT_ID = c.referenced_object_id AND column_id = c.referenced_column_id) [PK-Column]
, a.delete_referential_action
, a.delete_referential_action_desc
, a.update_referential_action
, a.update_referential_action_desc
-- , a.*
-- , c.*
FROM sys.foreign_keys a
JOIN sys.tables b ON a.parent_object_id = b.object_id
JOIN sys.foreign_key_columns c ON c.constraint_object_id = a.object_id
where b.name like @FK_Searched_Table
AND EXISTS (
SELECT [name] FROM [sys].tables
WHERE OBJECT_ID = c.referenced_object_id
AND [name] = @PK_Searched_Table
)
ORDER BY [PK-Table]

SET @LF = CHAR(13) + CHAR(10);

OPEN TestCursor

FETCH NEXT FROM TestCursor
INTO @ConstraintName
,@FK_TABLE
,@PK_TABLE
,@FK_Column
,@PK_Column
,@DeleteAction
,@DeleteActionDesc
,@UpdateAction
,@UpdateActionDesc


WHILE @@FETCH_STATUS = 0 BEGIN
IF @mode = 1 BEGIN
PRINT 'ALTER TABLE [dbo].[' + @FK_TABLE + '] WITH CHECK ADD CONSTRAINT [' + @ConstraintName + '] FOREIGN KEY([' + @FK_Column + '])
REFERENCES [dbo].[' + @PK_Table + '] ([' + @PK_Column + '])' +
CASE WHEN @DeleteAction <> 0 THEN @LF + ' ON DELETE ' + @DeleteActionDesc ELSE '' END +
CASE WHEN @UpdateAction <> 0 THEN @LF + ' ON UPDATE ' + @UpdateActionDesc ELSE '' END +
'
GO
ALTER TABLE [dbo].[' + @FK_TABLE + '] CHECK CONSTRAINT [' + @ConstraintName + ']
GO'
END ELSE IF @mode = 0 BEGIN
PRINT 'ALTER TABLE [dbo].[' + @FK_TABLE + ']
DROP CONSTRAINT ' + @ConstraintName + '
GO'
END

PRINT ''
FETCH NEXT FROM TestCursor
INTO @ConstraintName
,@FK_TABLE
,@PK_TABLE
,@FK_Column
,@PK_Column
,@DeleteAction
,@DeleteActionDesc
,@UpdateAction
,@UpdateActionDesc
END 
CLOSE TestCursor
DEALLOCATE TestCursor



Zur SysArc Homepage ...