• Be aware the Print command will only display approximately 8000 characters of text.

    Use the following script if you are scripting foreign keys for the entire database and need to save the output for later deployment:

    DECLARE @SchemaName VARCHAR(50) = NULL;

    DECLARE @TableName VARCHAR(250) = NULL;

    DECLARE @ColumnName VARCHAR(250) = NULL;

    SELECT SQLDrop = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = Object_ID(N''[' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + FK.name + ']'') AND parent_object_id =OBJECT_ID(N''[' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + ']'') ' + ') BEGIN ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] DROP CONSTRAINT [' + FK.name + ']' + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10)

    , SQLAdd = 'BEGIN ' + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT [FK_' + OBJECT_NAME(FK.Parent_object_id) + '_' + KeyOnC.name + '_' + SCHEMA_NAME(ReferencedT.SCHEMA_ID) + ReferencedT.NAME --+ '_' + ReferencedC.name

    + '] FOREIGN KEY(' + KeyOnC.NAME + ') REFERENCES ' + SCHEMA_NAME(ReferencedT.schema_id) + '.' + ReferencedT.NAME + '(' + ReferencedC.NAME + ')'

    --+ ' ON DELETE CASCADE'

    + CHAR(13) + CHAR(10) + 'END'

    , SQLEnable = 'BEGIN ' + CHAR(13) + CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(FK.SCHEMA_ID) + '].[' + OBJECT_NAME(FK.Parent_object_id) + '] WITH CHECK CHECK CONSTRAINT [FK_' + OBJECT_NAME(FK.Parent_object_id) + '_' + KeyOnC.name + '_' + SCHEMA_NAME(ReferencedT.SCHEMA_ID) + ReferencedT.NAME + ']' --+ '_' + ReferencedC.name

    + CHAR(13) + CHAR(10) + 'END'

    FROM sys.foreign_keys AS FK

    INNER JOIN sys.foreign_key_columns AS FKC

    ON fk.object_id = fkc.constraint_object_id

    INNER JOIN sys.columns AS KeyOnC

    ON FKC.Parent_object_id = KeyOnC.object_id

    AND FKC.Parent_column_id = KeyOnC.column_id

    INNER JOIN sys.columns AS ReferencedC

    ON FKC.referenced_object_id = ReferencedC.object_id

    AND FKC.referenced_column_id = ReferencedC.column_id

    INNER JOIN sys.objects AS KeyOnT

    ON KeyOnC.object_id = KeyOnT.object_id

    INNER JOIN sys.objects AS ReferencedT

    ON ReferencedC.object_id = ReferencedT.object_id

    WHERE ( SCHEMA_NAME(KeyOnT.schema_id) = @SchemaName

    OR @SchemaName IS NULL

    )

    AND ( KeyOnT.name LIKE '%' + @TableName + '%'

    OR ReferencedT.name LIKE '%' + @TableName + '%'

    OR @TableName IS NULL

    )

    AND ( KeyOnC.Name = @ColumnName

    OR ReferencedC.name = @ColumnName

    OR @ColumnName IS NULL

    )

    Wes
    (A solid design is always preferable to a creative workaround)