SQL Server’da İlgili Veritabanının Tüm Foreign Key’lerinin Yedeğini Almak

Herkese merhaba,

Bu yazıda SQL Server’da ilgili veritabanının tüm Foreign Key’lerinin yedeğinin nasıl alınacağı hakkında bilgi vereceğim.

SQL Server’da bazı durumlarda ilgili veritabanının tüm Foreign Key’lerinin yedeğini almak isteyebilirsiniz.

Aşağıdaki kodu kullanarak siz de bu işlemi rahatlıkla yapabilirsiniz.

DECLARE @schema_name AS SYSNAME;
DECLARE @table_name AS SYSNAME;
DECLARE @constraint_name AS SYSNAME;
DECLARE @constraint_object_id AS INT;
DECLARE @referenced_object_name AS SYSNAME;
DECLARE @is_disabled AS BIT;
DECLARE @is_not_for_replication AS BIT;
DECLARE @is_not_trusted AS BIT;
DECLARE @delete_referential_action AS TINYINT;
DECLARE @update_referential_action AS TINYINT;
DECLARE @tsql AS NVARCHAR (4000);
DECLARE @tsqlPK AS NVARCHAR (4000);
DECLARE @tsqlFK AS NVARCHAR (4000);
DECLARE @fkCol AS SYSNAME;
DECLARE @pkCol AS SYSNAME;
DECLARE @col1 AS BIT;
DECLARE @referenced_schema_name AS SYSNAME;

DECLARE FKcursor CURSOR
    FOR SELECT DISTINCT OBJECT_SCHEMA_NAME(f.parent_object_id),
                 OBJECT_NAME(f.parent_object_id),
                 name,
                 OBJECT_NAME(f.referenced_object_id),
                 OBJECT_ID,
                 is_disabled,
                 is_not_for_replication,
                 is_not_trusted,
                 delete_referential_action,
                 update_referential_action,
                 OBJECT_SCHEMA_NAME(f.referenced_object_id)
        FROM     sys.foreign_keys f WITH (NOLOCK)
        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
        BEGIN
            SET @tsql = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WITH CHECK ';

			SET @tsqlPK = '';
			SET @tsqlFK = '';
			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 AS fk WITH (NOLOCK)
								INNER JOIN
								sys.foreign_key_columns AS fkc WITH (NOLOCK)
								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)
					BEGIN
						SET @col1 = 0;
					END
					ELSE
					BEGIN
						SET @tsqlPK = @tsqlPK + ',';
						SET @tsqlFK = @tsqlFK + ',';
					END;

					SET @tsqlPK = @tsqlPK + QUOTENAME(@fkCol);
					SET @tsqlFK = @tsqlFK + QUOTENAME(@pkCol);

					FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
				END;
			CLOSE ColumnCursor;
			DEALLOCATE ColumnCursor;

			SET @tsql = @tsql + ' ADD CONSTRAINT ' + QUOTENAME('FK_' + @table_name + '_' + @referenced_object_name +
				CASE 
					WHEN @tsqlPK = @tsqlFK  THEN '' 
					ELSE '_' + REPLACE(REPLACE(REPLACE(@tsqlPK, ',', '_'), ']', ''), '[', '')
				END
				) + ' FOREIGN KEY (' + @tsqlPK + ' ) ';
			SET @tsql = @tsql + ' REFERENCES ' + QUOTENAME(@referenced_schema_name) + '.' + QUOTENAME(@referenced_object_name) + ' (' + @tsqlFK + ')';
			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 '' 
			END + ';';
        END;

        PRINT @tsql;

        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;

Yukarıdaki kodu ilgili veritabanında çalıştırdığınızda aşağıdakine benzer bir sonuç göreceksiniz.

Görüldüğü üzere ilgili veritabanının tüm Foreign Key yedekleri alınmış oldu.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.