Jedną z funkcjonalności SQL Server Management Studio (SSMS) jest możliwość wygenerowania skryptu dowolnego obiektu, w tym także wszystkich obiektów bazy danych wraz ze skryptem tworzącym tą bazę. W ten sposób można sklonować istniejąca bazę otrzymując skrypt ją inicjujący, który można wykorzystać jako element instalacji oprogramowania, którego baza jest częścią. Jak wiadomo baza w procesie produkcji oprogramowania ulega zmianom, zatem co jakiś czas konieczna jest aktualizacja takiego skryptu w celu przebudowania programu instalacyjnego. I tu pojawia się pierwsza trudność, konieczne jest uruchomienie SSMS, wybranie bazy, zażądanie dla niej wygenerowania skryptu, ustawienie odpowiednich opcji jego generowania i koniec końców wygenerowanie. Ponieważ jest to czynność powtarzalna, więc konieczne staje się zanotowanie sobie chociażby opcji. Dodatkowo powstały skrypt służy do założenia bazy o takiej samej nazwie, jak ta która posłużyła do jego wygenerowania, konieczna więc staje się jego edycja w celu naniesienia stosownych poprawek. Może być też tak, że treść elementów programowych serwera (procedury, funkcje, wyzwalacze, widoki) jest w bazie źródłowej zakodowana, aby zapobiec pobieraniu tej treści w celu modyfikacji przez programistów, z pominięciem repozytorium kodu i generując skrypt trzeba pominąć generowanie tych elementów, a następnie dołączyć je do skryptu z repozytorium (i to w odpowiednich miejscach – nie zaś na końcu, jak byłoby najwygodniej). Koniec końców trzeba to wykonać ręcznie co zajmuje czas i wymaga znajomości SSMS (albo dokumentu ze wskazówkami ,jak to w SSMS wykonać).

Takie rozwiązanie z pewnością nie jest zadowalające. Najlepiej byłoby, aby istniał automat generujący taki skrypt w oczekiwanej postaci. Mógłby go uruchomić ktokolwiek, nawet inny automat, którego zadaniem jest zbudowanie programu instalacyjnego.

I tutaj z pomocą przychodzi SQL Server Management Objects (SMO) – kolekcja obiektów przeznaczonych do programowego zarządzania Microsoft SQL Serwerem. Aby móc skorzystać z SMO konieczne jest dodanie do projektu referencji do następujących zestawów (dostępnych w: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\):

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

zaś w samym kodzie użycia następujących przestrzeni nazw:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;

Z zestawu udostępnianych przez SMO obiektów Do wygenerowania skryptu potrzebne będą obiekty następujących klas:

  • ServerConnection – odpowiedzialna za połączenie do serwera i pozwalająca ustalić rodzaj autoryzacji (z poświadczeniami Windows czy SQL Serwer)
  • Server – reprezentacja serwera, pozwala uzyskać dostęp do zarządzanych przez niego zasobów (m.in. baz danych)
  • Database – wybrana baza danych udostępniająca swoje obiekty (tabele, widoki, procedury, funkcje, itd.)
  • Table – tabela bazy danych
  • Schema – wybrany schemat z zestawu schematów bazy danych
  • ScriptingOptions – opcje konfigurujące klasę generującą skrypt
  • Scripter – generator skryptów

Nic nie stoi już na przeszkodzie, aby napisać kod klasy generującej skrypt. Generowana będzie jedynie struktura tabel oraz tworzone wszystkie schematy użytkownika (zakładam, że baza danych jest generowana dedykowanym skryptem, ze względu na inną nazwę i specyficzne ustawienia, chociażby jej początkowego rozmiaru). Znaczenie poszczególnych fragmentów kodu opisałem w komentarzach.

class ScriptGenerator
{
	#region pola
	private Server server;
	private Database dataBase;
	#endregion
	#region konstruktor
	public ScriptGenerator(string serverName, string dataBase, string login, string password)
	{
		ServerConnection connection;
		if (login == "") // jeżeli nie podano loginu - użyj poświadczeń Windows
			connection = new ServerConnection(serverName);
		else // w przeciwnym wypadku - logowanie za pomocą mechanizmów SQL Serwera
			connection = new ServerConnection(serverName, login, password);
		// utwórz instancję klasy serwera używając ustalonego wyżej połączenia
		server = new Server(connection);
		// wybór bazy danych
		this.dataBase = server.Databases[dataBase];
	}
	#endregion
	#region metody
	public void Generate(string fileName)
	{
		// poninformowanie obiektu, aby zadbał o informacje,
		// który obiekt jest systemowy i nie pobierał ich na bieżąco
		// co ma znaczący wpływ na wydajność przetwarzania pętli
		server.SetDefaultInitFields(typeof(Schema), "IsSystemObject");
		// utworzenie obiektu, który posłuży do generowania skyptu
		Scripter scripter = new Scripter(server);
		// dokonanie ustawień obiektu skryptującego
		SetOptions(scripter.Options, fileName);
		// przygotowanie tablicy na kolekcję tabel
		Table[] tables = new Table[dataBase.Tables.Count];
		// przekopiowanie kolekcji tabel bazy danych do tablicy
		dataBase.Tables.CopyTo(tables, 0);
		// przygotowanie listy na schematy
		List<Schema> schemas = new List<Schema>();
		// wypełnienie tejże listy
		foreach (Schema itm in dataBase.Schemas)
			if (!itm.IsSystemObject) // wyłącznie schematy niesystemowe - użytkownika
				schemas.Add(itm);
		// wygenerowanie skryptu tworzących schematy
		scripter.Script(schemas.ToArray());
		// uzupełnienie skryptu o utworzenie tabel
		scripter.Script(tables);
	}
	private void SetOptions(ScriptingOptions options, string fileName)
	{
		options.AnsiPadding = false;
		// generowane mają być tylko polecenie tworzące obiekty, nie ma potrzeby
		options.ScriptDrops = false;
		// należy utworzyć wszystkie niezbędne indeksy
		options.Indexes = true;
		// należy utworzyć wszystkie więzy
		options.DriAllConstraints = true;
		// załącz właściwości obiektów (jak np. opisy kolumn, tabel, itd.)
		options.ExtendedProperties = true;
		// skrytp ma używać składni dla SQL 2008R2
		options.TargetServerVersion = SqlServerVersion.Version105;
		// nie załączaj obiektów systemowych
		options.AllowSystemObjects = false;
		// kwalifikuj wszystkie obiekty nazwą schematu
		options.SchemaQualify = true;
		// odwołania do kluczy obcych też ze schematem
		options.SchemaQualifyForeignKeysReferences = true; 
		// nazwa pliku, do którego zostanie zapisany wygenerowany skrypt
		options.FileName = fileName;
		// ma być on generowany wyłącznie do pliku
		options.ToFileOnly = true;
		// treść ma być zakodowana w Ansi (a nie w Unicode), co ułatwia przetwarzanie przez wsady procesora poleceń 
		options.AnsiFile = true;
		// kolejne fragmenty skryptu mają być dodawane do pliku, aby powstał tylko jeden plik
		options.AppendToFile = true;
		// kodowanie znaków narodowych z pliku zgodne z kodowaniem systemowym
		options.Encoding = Encoding.Default;
	}
	#endregion
}

Należy nadmienić, że zawarte w metodzie SetOptions opcje są bezpośrednim odzwierciedleniem tych dostępnych z poziomu kreatora skryptu SSMS. Jeśli zatem skrypt ma zostać wygenerowany z innymi ustawieniami, wystarczy przetrenować jego generowanie w SSMS i po uzyskaniu zadowalającego efektu po prostu przepisać. Chciałbym jednak ostrzec przed jedną z opcji, tj. WithDependencies, która teoretycznie powinna generować skrypt wraz z obiektami zależnymi, ale zdarza się (co zależy od specyfiki bazy), że SMO sobie z tym nie radzi (tak w SSMS jak i z poziomu kodu). Otrzymany skrypt zawiera wówczas jedynie komentarza o niemożności uzyskania danego obiektu (bywa, że w ogóle nie można go odnaleźć w bazie przeszukując widok sys.objects).

Jeśli miałyby być dodatkowo generowane pozostałe obiekty bazy danych, to po wygenerowaniu schematów należy wygenerować (do tego samego pliku) typy, funkcje i procedury, następnie tabele, a po nich wyzwalacze i widoki (z ewentualnymi ich indeksami). Jeśli elementy te nie będą generowane, ale pobierane z repozytorium (bezpośrednio lub z dysku, gdzie je z niego wyodrębniono) to kolejność ich dopisywania do skryptu powinna być taka sama (należy zadbać o kolejność dodawania ma typów, aby złożone używające prostych były za nimi).

Wygenerowany skrypt można wykonać w SSMS (jeśli baza ma np. służyć nowemu programiście w jego „piaskownicy”), albo wykonać programowo – także za pomocą SMO, albowiem należy pamiętać, że wygenerowany skrypt posiada słowo GO, nie będące poleceniem SQL, więc standardowe mechanizmy wykonywania skryptów (SqlCommand) nie nadają się do jego wykonania.

Poniżej kod metody wykonującej taki skrypt (należy nim uzupełnić powyższą klasę i wywołać w konstruktorze metodę ConfigureConnectionContext).

public void ExecuteScript(string script)
{
	server.ConnectionContext.ExecuteNonQuery(script);
}
public void ConfigureConnectionContext(string dataBase)
{
	// tryb wykonania skryptu odpowiadający trybowi jego wykonywania przez SSMS
	this.server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
	this.server.ConnectionContext.DatabaseName = dataBase;
	this.server.UserOptions.AnsiNulls = true;
}

Aha! Wygenerowany skrypt można także wykonać za pomocą programu SQLCmnd :).