[PL] Skryptowanie w SQL Server 2008 – Klucze obce

Intro

To już trzeci wpis z serii “Skryptowanie w SQL Server 2008″. Dwa poprzednie dostępne są tu:

• [PL] Skryptowanie w SQL Server 2008 – Obiekty proceduralne
• [PL] Skryptowanie w SQL Server 2008 – Proste typy danych użytkownika

Tym razem przedstawiam sposób na skryptowanie kluczy obcych. Kiedy takie skryptowanie może się przydać? Na przykład wtedy, gdy chcemy usunąć, a następnie odtworzyć owe klucze (typowy scenariusz: import wyczyszczonych i zwalidowanych danych). W podsumowaniu podam, jakie rozwinięcia mojego kodu można zaimplementować, by za pomocą praktycznie tego samego kodu wykonywać inne zadania związane z kluczami obcymi.

Skryptowanie kluczy obcych

Zadanie: zeskryptować wszystkie klucze obce w bazie danych.

Rozwiązanie:

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;

-- tu będziemy trzymać generowany kod
DECLARE @SQL TABLE (
  LineId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Line nvarchar(4000) NOT NULL
);

DECLARE
  @object_name sysname,
  @object_id int,
  @object_schema_name sysname,
  @parent_object_name sysname,
  @parent_object_schema_name sysname,
  @is_not_for_replication bit,
  @delete_referential_action int,
  @update_referential_action int,
  @referenced_object_schema_name sysname,
  @referenced_object_name sysname,
  @column_name sysname,
  @referenced_column_name sysname,
  @columns nvarchar(4000),
  @referenced_columns nvarchar(4000);

-- kursor do wyciągania pojedynczych kluczy obcych
DECLARE CursorFK CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT
    name,
    [object_id],
    SCHEMA_NAME([schema_id]),
    OBJECT_SCHEMA_NAME(parent_object_id),
    OBJECT_NAME(parent_object_id),
    OBJECT_SCHEMA_NAME(referenced_object_id),
    OBJECT_NAME(referenced_object_id),
    is_not_for_replication,
    delete_referential_action,
    update_referential_action
  FROM sys.foreign_keys
  WHERE is_ms_shipped = 0;
OPEN CursorFK;
FETCH NEXT FROM CursorFK
INTO @object_name, @object_id, @object_schema_name,
     @parent_object_schema_name, @parent_object_name,
     @referenced_object_schema_name, @referenced_object_name,
     @is_not_for_replication,
     @delete_referential_action, @update_referential_action;

WHILE @@FETCH_STATUS = 0 BEGIN
  SELECT @columns = '', @referenced_columns = '';

  -- generujemy sprawdzenie, czy klucz nie istnieje
  INSERT INTO @SQL (Line)
  SELECT 'IF OBJECT_ID(N''' +
        REPLACE(
          QUOTENAME(@object_schema_name) + '.' +
          QUOTENAME(@object_name), '''', ''''''
        ) + ''', N''F'') IS NULL';

  -- generujemy polecenie ALTER TABLE ... ADD CONSTRAINT
  INSERT INTO @SQL (Line)
  SELECT '  ALTER TABLE ' +
    QUOTENAME(@parent_object_schema_name) + '.' +
    QUOTENAME(@parent_object_name);
  INSERT INTO @SQL (Line)
  SELECT '  ADD CONSTRAINT ' + QUOTENAME(@object_name);

  -- kursor do wyciągania kolejnych kolumn klucza obcego
  DECLARE CursorFKColumns CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR
    SELECT
      pc.name,
      rc.name
    FROM sys.foreign_key_columns AS f
    INNER JOIN sys.columns AS pc
    ON f.parent_object_id = pc.[object_id]
    AND f.parent_column_id = pc.column_id
    INNER JOIN sys.columns AS rc
    ON f.referenced_object_id = rc.[object_id]
    AND f.referenced_column_id = rc.column_id
    WHERE f.constraint_object_id = @object_id
    ORDER BY f.constraint_column_id
  OPEN CursorFKColumns;
  FETCH NEXT FROM CursorFKColumns
  INTO
    @column_name,
    @referenced_column_name;
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- doklejamy do zestawów kolumn
    SET @columns = @columns + ', ' + QUOTENAME(@column_name);
    SET @referenced_columns = @referenced_columns + ', ' + QUOTENAME(@referenced_column_name);

    FETCH NEXT FROM CursorFKColumns
    INTO
      @column_name,
      @referenced_column_name;
  END;
  CLOSE CursorFKColumns;
  DEALLOCATE CursorFKColumns;

  -- generujemy kod z listami kolumn
  INSERT INTO @SQL (Line)
  SELECT '  FOREIGN KEY (' + STUFF(@columns, 1, 2, '') + ')';
  INSERT INTO @SQL (Line)
  SELECT '  REFERENCES ' +
    QUOTENAME(@referenced_object_schema_name) + '.' +
    QUOTENAME(@referenced_object_name) + '(' +
    STUFF(@referenced_columns, 1, 2, '') + ')';

  -- akcje klucza przy usuwaniu wierszy
  IF @delete_referential_action <> 0 BEGIN
    INSERT INTO @SQL (Line)
    SELECT '  ON DELETE ' +
      CASE @delete_referential_action
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
      END;
  END;

  -- akcje klucza przy modyfikacji wierszy
  IF @update_referential_action <> 0 BEGIN
    INSERT INTO @SQL (Line)
    SELECT '  ON UPDATE ' +
      CASE @update_referential_action
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
      END;
  END;

  -- czy wymuszamy regułę klucza w replikacjach
  IF @is_not_for_replication = 1
    INSERT INTO @SQL (Line)
    SELECT '  NOT FOR REPLICATION';

  UPDATE @SQL
  SET Line = Line + ';'
  WHERE LineId = SCOPE_IDENTITY();

  INSERT INTO @SQL (Line) SELECT 'GO';

  FETCH NEXT FROM CursorFK
  INTO @object_name, @object_id, @object_schema_name,
     @parent_object_schema_name, @parent_object_name,
     @referenced_object_schema_name, @referenced_object_name,
     @is_not_for_replication,
     @delete_referential_action, @update_referential_action;
END;
CLOSE CursorFK;
DEALLOCATE CursorFK;
SELECT Line AS ' ' FROM @SQL ORDER BY LineId;
GO

Fragment wyniku:

Uwagi: w Management Studio ustaw Results to Text, sugeruję też zwiększenie maksymalnej długości tekstu w jednej kolumnie zwracanej w wynikach zapytań (w menu głównym kliknij Tools – Options – Query Results – SQL Server – Results to Text i w polu Maximum number of characters displayed in each column wpisz 8192, a następnie uruchom ponownie Management Studio).

Komentarz do kodu:

• metadane kluczy obcych zwracają widoki sys.foreign_keys (metadane tabeli, w której tworzymy klucz, opcje dla poleceń DELETE / UPDATE, opcja wyłączenia klucza dla replikacji) oraz sys.foreign_key_columns (metadane kolumn oraz tabeli, do której odwołuje się klucz obcy),
• funkcja QUOTENAME jak zwykle służy mi do otaczania identyfikatorów nawiasami kwadratowymi i apostrofami,
• fragmenty generowanego skryptu T-SQL są dodawane w kursorze (iteracja = definicja jednego obiektu) do zmiennej tabelarycznej @SQL (linie są numerowane autonumeracją w kolumnie LineId, zakładam maksymalną długość jednej linii kodu w obiekcie na 4000 znaków),
• rozwiązanie oparłem o dwa kursory: CursorFK – do iterowania po kluczach obcych oraz zagnieżdżony CursorFKColumns – do iterowania po kolumnach bieżącego klucza,
• ponieważ nie jestem zwolennikiem wyłączania więzów, moje rozwiązanie nie uwzględnia kolumny is_disabled z widoku sys.foreign_keys (nie generuję klauzuli WITH NOCHECK nawet, gdy klucz jest wyłączony).

Słowo podsumowania

Powyższy kod można rozwinąć:

• można dołożyć filtrowanie po tabelach (czy to tabeli, w której klucz jest utworzony, czy po tabeli, do której klucz się odwołuje),
• można generować tylko składnie ALTER TABLE … DROP CONSTRAINT, jeżeli zależy nam tylko na usunięciu wybranych kluczy,
• można wykorzystać kolumnę is_system_named z widoku sys.foreign_keys, jeżeli chcemy odnaleźć te klucze, które otrzymały nazwy wygenerowane systemowo (jeżeli nie chcemy mieć w bazie danych obiektów nazywanych dość przypadkowo).

Na koniec informacja – w repozytorium kodu umieściłem kod pseudo-systemowej procedury sp_dropforeignkeys, która może posłużyć do wygenerowania kodu usuwającego klucze obce wskazujące na wybraną tabelę. W procedurze tej wykorzystałem te same obiekty systemowe, co w powyższym kodzie, więc komentarz do niej wydaje mi się zbędny. Po stworzeniu w bazie master i oznaczeniu jako obiekt systemowy procedura powinna działać w kontekście dowolnej bazy danych.