Автоматическое скриптование объектов. Объектная модель SMO.

Рассмотрим задачу автоматизированного получения скрипта на создание объекта базы данных. Если это программируемый объект, например, процедура, функция, представление или что-то подобное, то можно воспользоваться хранимой процедурой sp_helptext. Или же можно сделать запрос к каталогу sys.sql_modules. А что делать, если требуется скрипт на создание объекта, код которого не хранится sys.sql_modules? Например, надо автоматически заскриптовать таблицу, индекс или объект Service Broker-а. Или же полнотекстовый каталог, базу данных или еще что-нибудь подобное.

Конечно, для каждого из этих объектов есть свой каталог с метаданными. Например, для таблицы это sys.tables, sys.columns, sys.types, для индекса - sys.indexes, sys.index_columns, sys.columns. Для очередей Service Broker-а есть каталог sys.service_queues и так далее.

Однако при таком подходе потребуется долгий и мучительный анализ различных столбцов этих каталогов для учета всех настроек. Причем содержимое этих каталогов может меняться от версии к версии, и за этим надо также следить. Например, при создании кластерного или некластерного индекса требуется отследить ключевые столбцы, порядок их сортировки, включенные столбцы, файловую группу, опции индекса. Также надо учесть возможность пробельных символов в именах.

Хочу предложить технически более простой способ скриптования объектов. Это можно делать с помощью объектной модели SMO. Эта модель позволяет не только скриптовать объекты, но и осуществлять полноценное администрирование сервера: создавать и редактировать объекты, делать запросы и пр.

Конечно, в рамках этой модели для каждого типа объектов надо писать отдельную ветку кода для вызова метода объекта соответствующего класса (для таблиц Tables, для баз данных Databases и так далее). Но зато не нужно анализировать свойства объектов. Нужно лишь создать экземпляр нужного класса и вызвать метод Script. Рассмотрим работу этого метода на примере скриптования таблиц. Правильнее всего было бы сделать clr-сборку и вызывать ее на сервере. Однако Microsoft запрещает обращаться к SMO в clr-сборках, поэтому для начала напишем консольное приложение, которое будет получать через командную строку информацию о таблице и выводить скрипт создания таблицы.

Приступим к кодингу. Создадим проект консольного приложения. Необходимо добавить ссылки на библиотеки из файлов Microsoft.SqlServer.Smo.dll, Microsoft.SqlServer.Management.Sdk.Sfc.dll, Microsoft.SqlServer.ConnectionInfo.dll. На моем компьютере они находятся в каталоге C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies. Ниже представлен полный исходный код приложения:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace SmoTasks

{

    class TablesWrk

    {

        static void Main(string[] args)

        {

            string output, SrvName = args[0], DbName = args[1], SchName = args[2], ObjName = args[3];

            try

            {

                ServerConnection srvConn = new ServerConnection();

                srvConn.ServerInstance = SrvName;

                srvConn.LoginSecure = true;

                srvConn.Connect();

                Server srv = new Server(srvConn);

                Database db = srv.Databases[DbName];

                Table tab = db.Tables[ObjName, SchName];

                ScriptingOptions opt = new ScriptingOptions();

                opt.DriAllConstraints = true;

                opt.DriAll = true;

                opt.Indexes = true;

                opt.NoCollation = false;

                opt.XmlIndexes = true;

                opt.Statistics = true;

                opt.IncludeIfNotExists = true;

                StringCollection tabScr = tab.Script(opt);

                output = "";

                foreach (string str in tabScr)

                {

                    output = output + "\n" + str;

                }

                Console.WriteLine(output);

            }

            catch(Exception ex)

            {

                Console.WriteLine("error" + "\n" + ex.ToString());

            }

        }

    }

}

В этом коде мы создаем объект Table и используем его метод Script. Точно также можно поступать, если требуется создать любой другой объект. Перед вызовом метода Script задаются опции скриптования. Опция DriAll означает, что в определение таблицы включаются все ограничения целостности данных, Indexes, XmlIndexes включают определения всех обычных и xml-индексов. Установив NoCollation в false, мы получим вместе с определениями тестовых столбцов имена параметров их сортировки. Опция Statistics, как следует из ее названия, предоставит все статистики таблицы. Очень интересна опция IncludeIfNotExists. Благодаря ей, в скрипте перед кодом создания объекта в блоке if будет проверяться существует ли он.

Теперь можно написать хранимую процедуру, которая будет вызывать данное приложение через xp_cmdshell. Результаты, которые выводятся через Console.WriteLine, можно поймать с помощью конструкции insert into ... exec xp_cmdshell ...
Ниже полный код процедуры:

if sessionproperty ( N'quoted_identifier' ) = 0

       set quoted_identifier on

go

if sessionproperty ( N'ansi_nulls' ) = 0

       set ansi_nulls on

go

if object_id ( N'dbo.TabScriptGet', N'P' ) is null

       exec ( N'create proc dbo.TabScriptGet as return' )

go

alter proc dbo.TabScriptGet

(

       @TabName     nvarchar ( 200 ),

       @Script             nvarchar ( max ) out

)

as

begin

       set nocount, xact_abort on

       declare @SchName nvarchar ( 200 ) = isnull ( parsename ( @TabName, 2 ), N'dbo' ), @SrvName nvarchar ( 200 ) = @@servername, @DbName nvarchar ( 200 ) = db_name (), @cmd varchar ( 8000 ),

             @file nvarchar ( 200 ) = 'C:\V\Временные\ConsoleApplication1\ConsoleApplication1\bin\Debug\ConsoleApplication1.exe', @log nvarchar ( max )

      

       if object_id ( @TabName, N'U' ) is null

       begin

             raiserror ( N'Таблица с именем "%s" не найдена.', 16, 1, @TabName )

             return

       end

       set @TabName = parsename ( @TabName, 1 )

       set @cmd = N'chcp 866 & ' + @file + N' ' + @SrvName + N' ' + @DbName + N' ' + @SchName + N' ' + @TabName

       if object_id ( N'tempdb..#res', N'U' ) is not null

             drop table #res

       create table #res

       (

             iRowId int identity ( 1, 1 )      not null,

             val          nvarchar ( max )           null

       ) on [PRIMARY]

       insert into #res ( val )

             exec master.dbo.xp_cmdshell @cmd

       delete #res

             where val is null

       delete #res

             where val like 'Active code page%'

       set @Script = replace ( cast

             (

                    (

                           select val + nchar ( 13 ) as [text()]

                           from #res

                           for xml path ( N'' )

                    )

                    as nvarchar ( max )

             ), N'
', nchar ( 13 ) )

      

       if exists

       (

             select 1

             from #res

             where val = N'error'

       )

       begin

             set @log = @Script

             set @log = replace ( cast

             (

                    (

                           select val + nchar ( 13 ) as [text()]

                           from #res

                           where val <> N'error'

                           for xml path ( N'' )

                    )

                    as nvarchar ( max )

             ), N'&#x0D;', nchar ( 13 ) )

             set @Script = null

             raiserror ( @log, 16, 1 )

             return

       end

end

go

В этом коде при формировании команды мы предварительно меняем кодовую страницу, чтобы получить русские буквы в читабельном виде. Если скрипт состоит из нескольких строк, то все они через инструкцию insert into ... exec ... попадут в таблицу #res в виде отдельных строк. Чтобы получить их конкатенацию используется запрос в #res с опцией for xml path. Это пожалуй самый быстрый способ конкатенации строк в sql. На случай, если в приложении произошло исключение, делается проверка на наличие строки error в #res. В этом случае вычисляется полный текст сообщения об ошибке и он поднимается наверх.
Испытаем процедуру в действии. Создадим такую таблицу:

create table test.Accounts

(

       i      sql_variant,

       h      datetime,

       q      varchar ( 200 )

)

create nonclustered index IX on test.Accounts ( i )

alter table table test.Accounts add constraint DF default ( '2000-01-01' ) for h

В код на создание таблицы я намеренно не включил многие опции, которые используются по умолчанию, чтобы дополнительно удостовериться, что метод Script их всех учтет:

declare @TabName nvarchar ( 200 ), @Script nvarchar ( max )

exec dbo.TabScriptGet 'test.Accounts', @Script out

select @Script