Replication Script

/*
Here we update all the FKs which don't currently have the NFR flag set.
This query returns the script to be run and it will only work for singleton PKs ie no composite keys
*/

IF Object_id('tempdb..#mysql') IS NOT NULL
  DROP TABLE #mysql

CREATE TABLE #mysql
  (
     id      INT,
     mainsql VARCHAR(8000)
  )

INSERT INTO #mysql
VALUES     (1,'Set XACT_ABORT ON')

INSERT INTO #mysql
VALUES     (5,'BEGIN TRAN')

INSERT INTO #mysql
SELECT 100,
       'ALTER TABLE ' + s.name + '.' + tabs1.name
       + ' DROP CONSTRAINT ' + fks.name
       + ';ALTER TABLE ' + s.name + '.' + tabs1.name
       + ' WITH CHECK ADD CONSTRAINT ' + fks.name
       + ' FOREIGN KEY('
       + Col_name(cols.parent_object_id, parent_column_id)
       + ') REFERENCES ' + s2.name + '.' + tabs2.name + '('
       + Col_name(cols.referenced_object_id, cols.referenced_column_id)
       + ') ' + CASE WHEN fks.delete_referential_action = 0 THEN
       ' ON DELETE NO ACTION ' WHEN fks.delete_referential_action = 1 THEN
       ' ON DELETE CASCADE ' WHEN fks.delete_referential_action = 2 THEN
       ' ON DELETE SET NULL ' WHEN fks.delete_referential_action = 3 THEN
       ' ON DELETE SET DEFAULT ' ELSE '' END +
       CASE WHEN fks.update_referential_action = 0 THEN ' ON UPDATE NO ACTION '
            WHEN fks.update_referential_action = 1 THEN ' ON UPDATE CASCADE '
            WHEN fks.update_referential_action = 2 THEN ' ON UPDATE SET NULL '
            WHEN fks.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT '
            ELSE ''
       END + ' NOT FOR REPLICATION'
FROM   sys.foreign_keys fks
       INNER JOIN sys.foreign_key_columns cols
               ON fks.object_id = cols.constraint_object_id
       INNER JOIN sys.tables tabs1
               ON fks.parent_object_id = tabs1.object_id
       INNER JOIN sys.schemas s
               ON tabs1.schema_id = s.schema_id
       INNER JOIN sys.tables tabs2
               ON cols.referenced_object_id = tabs2.object_id
       INNER JOIN sys.schemas s2
               ON tabs2.schema_id = s2.schema_id
WHERE  is_not_for_replication = 0
       AND tabs2.is_ms_shipped = 0
       AND cols.constraint_object_id NOT IN (SELECT constraint_object_id
                                             FROM   sys.foreign_key_columns
                                             GROUP  BY constraint_object_id
                                             HAVING
           Count(constraint_column_id) > 1)

INSERT INTO #mysql
VALUES     (1000,
            'COMMIT TRAN')

SELECT mainsql
FROM   #mysql
ORDER  BY id ASC

/*
This is version 2 that deals with composite FKs
*/

DECLARE @schema_name SYSNAME ;
DECLARE @table_name SYSNAME ;
DECLARE @constraint_name SYSNAME ;
DECLARE @constraint_object_id INT ;
DECLARE @referenced_object_name SYSNAME ;
DECLARE @is_disabled BIT ;
DECLARE @is_not_for_replication BIT ;
DECLARE @is_not_trusted BIT ;
DECLARE @delete_referential_action TINYINT ;
DECLARE @update_referential_action TINYINT ;
DECLARE @tsql NVARCHAR(4000) ;
DECLARE @tsql2 NVARCHAR(4000) ;
DECLARE @fkCol SYSNAME ;
DECLARE @pkCol SYSNAME ;
DECLARE @col1 BIT ;
DECLARE @action CHAR(6) ;
DECLARE @referenced_schema_name SYSNAME ;

DECLARE FKcursor CURSOR
FOR
    SELECT  OBJECT_SCHEMA_NAME(parent_object_id) ,
            OBJECT_NAME(parent_object_id) ,
            name ,
            OBJECT_NAME(referenced_object_id) ,
            object_id ,
            is_disabled ,
            is_not_for_replication ,
            is_not_trusted ,
            delete_referential_action ,
            update_referential_action ,
            OBJECT_SCHEMA_NAME(referenced_object_id)
    FROM    sys.foreign_keys
    WHERE   is_not_for_replication = 0
    ORDER BY 1 ,2 ;OPEN FKcursor ;
    FETCH NEXT FROM FKcursor
    INTO @schema_name, @table_name, @constraint_name,
    @referenced_object_name, @constraint_object_id, @is_disabled,
    @is_not_for_replication, @is_not_trusted, @delete_referential_action,
    @update_referential_action, @referenced_schema_name ;
WHILE @@FETCH_STATUS = 0
    BEGIN

        IF @action <> 'CREATE'
            SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.'
                + QUOTENAME(@table_name) + ' DROP CONSTRAINT '
                + QUOTENAME(@constraint_name) + ';' ;
         ELSE
            BEGIN
                SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.'
                    + QUOTENAME(@table_name) + ' DROP CONSTRAINT '
                    + QUOTENAME(@constraint_name) + ';' + 'ALTER TABLE '
                    + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
                    + CASE @is_not_trusted
                        WHEN 0 THEN ' WITH CHECK '
                        ELSE ' WITH NOCHECK '
                      END + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)
                    + ' FOREIGN KEY (' ;
                 SET @tsql2 = '' ;
                 DECLARE ColumnCursor CURSOR FOR
                    SELECT  COL_NAME(fk.parent_object_id, fkc.parent_column_id) ,
                            COL_NAME(fk.referenced_object_id,
                                     fkc.referenced_column_id)
                    FROM    sys.foreign_keys fk
                            INNER JOIN sys.foreign_key_columns fkc
                            ON fk.object_id = fkc.constraint_object_id
                    WHERE   fkc.constraint_object_id = @constraint_object_id
                    ORDER BY fkc.constraint_column_id ;
                 OPEN ColumnCursor ;
                SET @col1 = 1 ;
                 FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol ;
                 WHILE @@FETCH_STATUS = 0
                    BEGIN
                        IF ( @col1 = 1 ) ;
                            SET @col1 = 0 ;
                        ELSE
                            BEGIN
                               SET @tsql = @tsql + ','
                                SET @tsql2 = @tsql2 + ',' ;
                            END ;
                        SET @tsql = @tsql + QUOTENAME(@fkCol) ;
                        SET @tsql2 = @tsql2 + QUOTENAME(@pkCol) ;
                        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol ;
                    END ;
                CLOSE ColumnCursor ;

                DEALLOCATE ColumnCursor ;
                 SET @tsql = @tsql + ' ) REFERENCES '
                    + QUOTENAME(@referenced_schema_name) + '.'
                    + QUOTENAME(@referenced_object_name) + ' (' + @tsql2 + ')' ;
                 SET @tsql = @tsql + ' ON UPDATE '
                    + CASE @update_referential_action
                        WHEN 0 THEN 'NO ACTION '
                        WHEN 1 THEN 'CASCADE '
                        WHEN 2 THEN 'SET NULL '
                        ELSE 'SET DEFAULT '
                      END + ' ON DELETE ' + CASE @delete_referential_action
                                              WHEN 0 THEN 'NO ACTION '
                                              WHEN 1 THEN 'CASCADE '
                                              WHEN 2 THEN 'SET NULL '
                                              ELSE 'SET DEFAULT '
                                            END
                    + CASE @is_not_for_replication
                        WHEN 1 THEN ' NOT FOR REPLICATION '
                        ELSE ' NOT FOR REPLICATION '
                      END + ';' ;
            END ;
        PRINT @tsql ;

        IF @action = 'CREATE'
            BEGIN
                SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.'
                    + QUOTENAME(@table_name) + CASE @is_disabled
                                                 WHEN 0 THEN ' CHECK '
                                                 ELSE ' NOCHECK '
                                               END + 'CONSTRAINT '
                    + QUOTENAME(@constraint_name) + ';' ;
                PRINT @tsql ;
            END ;

        FETCH NEXT FROM FKcursor INTO @schema_name, @table_name,
            @constraint_name, @referenced_object_name, @constraint_object_id,
            @is_disabled, @is_not_for_replication, @is_not_trusted,
            @delete_referential_action, @update_referential_action,
            @referenced_schema_name ;
    END ;
CLOSE FKcursor ;
DEALLOCATE FKcursor ;