[PL] Skryptowanie w SQL Server 2008 – Obiekty proceduralne

Pilot

Umiejętność korzystania z metadanych w celu skryptowania obiektów w bazach danych (tabel, procedur, itd.) przydaje się w życiu administratora i programisty baz danych. Sam wykorzystuję skryptowanie do cyklicznego tworzenia kopii kodu T-SQL obiektów w bazach danych (na wypadek, gdyby akurat okazało się, że w bazie uszkodzeniu uległ któryś z obiektów przechowujących metadane). Zapytasz, czemu nie użyć do tego mechanizmu skryptowania oferowanego przez Management Studio? Otóż skryptowanie z użyciem własnego kodu T-SQL daje mi więcej swobody, a ponadto skryptowanie w Management Studio ma swoje wady (patrz tutaj: [EN] SQL Server – Don’t (sp_)rename your database objects) i na dodatek często występują spore problemy w przypadku, gdy baza danych zawiera sporo obiektów (jakoś listowanie pokaźnej listy obiektów nie wychodzi narzędziu Management Studio – często aplikacja się zawiesza albo odpowiada wyjątkiem klasy OutOfMemoryException – patrz tutaj: Dziennik pokładowy MSSQL 2008 (wpis 0×0011) – Problem z Generate Script…).

Dlatego postanowiłem napocząć na blogu nową serię wpisów poświęconych tworzeniu własnego kodu T-SQL do skryptowania. Zaczynam od skryptowania obiektów proceduralnych – funkcji, procedur, widoków i wyzwalaczy. Potem spróbuję pokazać, jak można skryptować typy danych użytkownika, więzy, obiekty CLR i tabele. Tam, gdzie ilość kodu nie będzie wykraczała poza możliwości percepcji, będę się starał pokazać pełne rozwiązanie, ale na przykład w przypadku tabel już wiem, że nie będę podawał pełnego rozwiązania, a tylko podsunę kawałek kodu i parę pomysłów na dobry początek :-)

Kod będę tworzył z myślą o skryptowaniu w SQL Server 2008 i SQL Server 2008 R2. Przy odrobinie wysiłku kod będzie do zaadoptowania na potrzeby SQL Server 2005.

Funkcje, procedury, widoki

Zadanie: zeskryptować wszystkie funkcje, procedury i widoki w wybranej bazie danych.

Rozwiązanie:

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DECLARE @SQL TABLE (
  LineId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Line nvarchar(4000) NOT NULL
);
DECLARE
  @schema_name sysname,
  @object_name sysname,
  @type_desc nvarchar(60),
  @type char(2),
  @definition nvarchar(max),
  @pindex int;
DECLARE CursorObjects CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT
    SCHEMA_NAME(o.[schema_id]),
    o.name,
    o.type_desc COLLATE database_default,
    o.type COLLATE database_default,
    m.definition
  FROM sys.objects AS o
  INNER JOIN sys.sql_modules AS m
  ON m.[object_id] = o.[object_id]
  WHERE o.type IN ('FN', 'IF', 'P', 'TF', 'V')
  AND o.is_ms_shipped = 0
  ORDER BY SCHEMA_NAME(o.[schema_id]), o.name;
OPEN CursorObjects;
FETCH NEXT FROM CursorObjects
INTO
  @schema_name,
  @object_name,
  @type_desc,
  @type,
  @definition;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @pindex = 0;

  -- generujemy PRINT
  INSERT INTO @SQL (Line)
  SELECT 'PRINT N''-- ' + @type_desc + ': ' +
  REPLACE(
    QUOTENAME(@schema_name) + '.' +
    QUOTENAME(@object_name), '''', ''''''
  ) + '''';

  IF @definition IS NOT NULL
  BEGIN
    -- generujemy DROP
    INSERT INTO @SQL (Line)
    SELECT 'IF OBJECT_ID(N''' +
      REPLACE(
        QUOTENAME(@schema_name) + '.' +
        QUOTENAME(@object_name), '''', ''''''
      ) + ''', N''' + (@type) + ''') IS NOT NULL';
    INSERT INTO @SQL (Line) SELECT '  DROP ' +
      CASE
        WHEN @type = 'P' THEN 'PROCEDURE'
        WHEN @type IN ('FN', 'IF', 'TF') THEN 'FUNCTION'
        ELSE 'VIEW'
      END + ' ' +
      QUOTENAME(@schema_name) + '.' +
      QUOTENAME(@object_name) + ';';

    -- poprzedzamy definicję słowem GO  
    INSERT INTO @SQL (Line) SELECT 'GO';

    -- kroimy definicję na poszczególne wiersze
    SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) + '%', @definition);
    WHILE @pindex > 0 BEGIN
      INSERT INTO @SQL(Line)
      SELECT REPLACE(SUBSTRING(@definition, 1, @pindex), CHAR(13), '');
      SET @definition = SUBSTRING(@definition, @pindex + 2, LEN(@definition));
      SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) +'%', @definition);
    END;
  END;
  ELSE
  BEGIN
    INSERT INTO @SQL (Line)
    SELECT 'PRINT N''-- *** This object is encrypted ***''';
  END;

  -- kończymy definicję słowem GO
  INSERT INTO @SQL (Line) SELECT 'GO';  

  FETCH NEXT FROM CursorObjects
  INTO
    @schema_name,
    @object_name,
    @type_desc,
    @type,
    @definition;
END;
CLOSE CursorObjects;
DEALLOCATE CursorObjects;
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:

• definicje obiektów są trzymane w widoku sys.sql_modules w kolumnie definition, wyjątek stanowią obiekty z opcją WITH ENCRYPTION użytą w definicji (takie obiekty są “szyfrowane”),
• listę obiektów stworzonych przez użytkowników w bazie danych zwraca widok sys.objects (ale jest też widok sys.all_objects, w którym dodatkowo są zawarte metadane obiektów systemowych),
• 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),
• zapytanie filtruje obiekty wg typu – wybiera funkcje trzech rodzajów, procedury i widoki, jest też filtr odrzucający ewentualne obiekty systemowe (kolumna is_ms_shipped w widoku sys.objects),
• przed definicją każdego obiektu dorzucam kod wykrywający obecność obiektu w bazie danych i usuwający obiekt w przypadku, gdy takowy istnieje,
• kolumny type i type_desc z widoku sys.objects mają na sztywno przypisane collation, stąd użycie funkcji COLLATE (inaczej nie dałoby się sklejać tych kolumn z tekstem, gdy collation bazy jest inne niż collation wspomnianych kolumn),
• operuję zawsze dwuczłonowymi nazwami obiektów (dzięki temu nazwa jest jednoznaczna w kontekście bazy danych) – do uzyskania nazwy schematu obiektu wykorzystuję funkcję SCHEMA_NAME,
• funkcja REPLACE jest przeze mnie użyta do “wyescape’owania” apostrofów w nazwach obiektów (swoją drogą, jak ktoś używa niestandardowych identyfikatorów zawierających dziwne znaczki i/lub słowa kluczowe, sam prosi się o kłopoty),
• funkcja QUOTENAME pozwala otoczyć identyfikatory nawiasami kwadratowymi – zabezpieczam się przed niestandardowymi identyfikatorami.

Wyzwalacze na poziomie bazy

Zadanie: zeskryptować wszystkie wyzwalacze w wybranej bazie danych.

Rozwiązanie:

-- wyzwalacze DML i DDL (ON DATABASE)
USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DECLARE @SQL TABLE (
  LineId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Line nvarchar(4000) NOT NULL
);
DECLARE
  @schema_name sysname,
  @object_name sysname,
  @type_desc nvarchar(60),
  @parent_class int,
  @definition nvarchar(max),
  @pindex int;
DECLARE CursorObjects CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT
    OBJECT_SCHEMA_NAME(t.[object_id]),
    t.name,
    t.type_desc COLLATE database_default,
    t.parent_class,
    m.definition
  FROM sys.triggers AS t
  INNER JOIN sys.sql_modules AS m
  ON m.[object_id] = t.[object_id]
  WHERE t.is_ms_shipped = 0
  ORDER BY OBJECT_SCHEMA_NAME(t.[object_id]), t.name;
OPEN CursorObjects;
FETCH NEXT FROM CursorObjects
INTO
  @schema_name,
  @object_name,
  @type_desc,
  @parent_class,
  @definition;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @pindex = 0;

  -- generujemy PRINT
  INSERT INTO @SQL (Line)
  SELECT 'PRINT N''-- ' + @type_desc + ': ' +
  REPLACE(
    ISNULL(QUOTENAME(@schema_name) + '.', '') +
    QUOTENAME(@object_name), '''', ''''''
  ) + ''';';

  IF @definition IS NOT NULL
  BEGIN
    -- generujemy DROP
    INSERT INTO @SQL (Line)
    SELECT 'IF EXISTS (SELECT * FROM sys.triggers WHERE name = N''' +
      REPLACE(
        ISNULL(QUOTENAME(@schema_name) + '.', '') +
        QUOTENAME(@object_name),
        '''',
        ''''''
      ) + ''')';
    INSERT INTO @SQL (Line)
    SELECT '  DROP TRIGGER ' +
      ISNULL(QUOTENAME(@schema_name) + '.', '') +
      QUOTENAME(@object_name) +
      CASE WHEN @parent_class = 0 THEN ' ON DATABASE' ELSE '' END + ';';

    -- poprzedzamy definicję słowem GO  
    INSERT INTO @SQL (Line) SELECT 'GO';

    -- kroimy definicję na poszczególne wiersze
    SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) + '%', @definition);
    WHILE @pindex > 0 BEGIN
      INSERT INTO @SQL(Line)
      SELECT REPLACE(SUBSTRING(@definition, 1, @pindex), CHAR(13), '');
      SET @definition = SUBSTRING(@definition, @pindex + 2, LEN(@definition));
      SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) +'%', @definition);
    END;
  END;
  ELSE
  BEGIN
    INSERT INTO @SQL (Line)
    SELECT 'PRINT N''-- *** This object is encrypted ***''';
  END;

  -- kończymy definicję słowem GO
  INSERT INTO @SQL (Line) SELECT 'GO';  

  FETCH NEXT FROM CursorObjects
  INTO
    @schema_name,
    @object_name,
    @type_desc,
    @parent_class,
    @definition;
END;
CLOSE CursorObjects;
DEALLOCATE CursorObjects;
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:

• listę wyzwalaczy w bazie danych zwraca widok sys.triggers,
• 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),
• drobnym wyzwaniem było rozróżnienie wyzwalaczy DML i DDL – każdy z tych typów wyzwalaczy wymaga nieco innego kodu przy usuwaniu, posłużyłem się kolumna parent_class z widoku sys.triggers,
• ponieważ w widoku sys.triggers nie ma kolumny schema_id, musiałem użyć funkcji OBJECT_SCHEMA_NAME do wyłuskania nazwy schematu,
• przy okazji taka obserwacja – wyzwalacz DML można utworzyć tylko w tym samym schemacie, w którym znajduje się nadrzędna tabela/widok.

Wyzwalacze na poziomie serwera

Zadanie: zeskryptować wszystkie wyzwalacze DDL na poziomie serwera.

Rozwiązanie:

USE master;
GO
SET NOCOUNT ON;
DECLARE @SQL TABLE (
  LineId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Line nvarchar(4000) NOT NULL
);
DECLARE
  @object_name sysname,
  @type_desc nvarchar(60),
  @definition nvarchar(max),
  @pindex int;
DECLARE CursorObjects CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
  SELECT
    t.name,
    t.type_desc COLLATE database_default,
    m.definition
  FROM sys.server_triggers AS t
  INNER JOIN sys.server_sql_modules AS m
  ON m.[object_id] = t.[object_id]
  WHERE t.is_ms_shipped = 0
  ORDER BY t.name;
OPEN CursorObjects;
FETCH NEXT FROM CursorObjects
INTO
  @object_name,
  @type_desc,
  @definition;
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @pindex = 0;

  -- generujemy PRINT
  INSERT INTO @SQL (Line)
  SELECT 'PRINT N''-- ' + @type_desc + ': ' +
  REPLACE(
    QUOTENAME(@object_name), '''', ''''''
  ) + ''';';

  IF @definition IS NOT NULL
  BEGIN
    -- generujemy DROP
    INSERT INTO @SQL (Line)
    SELECT 'IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N''' +
      REPLACE(
        @object_name, '''', ''''''
      ) + ''')';
    INSERT INTO @SQL (Line)
    SELECT '  DROP TRIGGER ' +
      QUOTENAME(@object_name) + ' ON ALL SERVER;'

    -- poprzedzamy definicję słowem GO  
    INSERT INTO @SQL (Line) SELECT 'GO';

    -- kroimy definicję na poszczególne wiersze
    SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) + '%', @definition);
    WHILE @pindex > 0 BEGIN
      INSERT INTO @SQL(Line)
      SELECT REPLACE(SUBSTRING(@definition, 1, @pindex), CHAR(13), '');
      SET @definition = SUBSTRING(@definition, @pindex + 2, LEN(@definition));
      SET @pindex = PATINDEX('%' + CHAR(13) + CHAR(10) +'%', @definition);
    END;
  END;
  ELSE
  BEGIN
    INSERT INTO @SQL (Line)
    SELECT 'PRINT N''-- *** This object is encrypted ***''';
  END;

  -- kończymy definicję słowem GO
  INSERT INTO @SQL (Line) SELECT 'GO';  

  FETCH NEXT FROM CursorObjects
  INTO
    @object_name,
    @type_desc,
    @definition;
END;
CLOSE CursorObjects;
DEALLOCATE CursorObjects;
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:

• listę wyzwalaczy na poziomie serwera zwraca widok sys.server_triggers,
• definicje widoków na poziomie serwera znajdują się w kolumnie definition widoku sys.server_sql_modules,
• 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),
• tu było bodaj najłatwiej, ponieważ wyzwalacze na poziomie serwera nie mają przypisanego schematu.

Słowo podsumowania

Skryptowanie pojedynczego obiektu proceduralnego to nie problem. SQL Server oferuje procedurę sp_helptext oraz funkcję OBJECT_DEFINITION. Ale jeżeli chcemy hurtowo skryptować obiekty, podany w powyżej kod może okazać się przydatny. Można na jego podstawie zbudować własne procedury do skryptowania i używać ich na codzień. Tu polecam wpis z mojego starego bloga:  [PL][util] Piszemy narzędzia w T-SQL cz. 1 – sp_getcolumns.

Zachęcam do zabawy z metadanymi SQL Servera. Można się dzięki takiej zabawie sporo nauczyć. I oczywiście zachęcam do komentowania tego wpisu. Może masz jakieś uwagi, coś nie gra w podanym tu kodzie albo masz lepszy pomysł na realizację takich zadań.