Software script generation for MSSQL

Due to frequent and unordered changes in the database, by a large number of users, there are often questions about the history of changes. We are not talking about the total logging of all changes that occur to the base during the day. Of interest are images of the database structure every day after the end of the working day. Using SQL Server Management Studio you can generate scripts, but individually or all at once. Full freedom of action can be obtained by using a set of libraries from SQL Server Management Studio in your .NET application. Description of the script generation program: tables, views, procedures below.

Joining Libraries

You will need classes from namespaces:

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

The libraries that contain them have the same names and are in the folder:
C: \ Program Files \ Microsoft SQL Server \ 90 \ SDK \ Assemblies
or
C: \ Program Files \ Microsoft SQL Server \ 100 \ SDK \ Assemblies
depending on the version of SQL Server.

Executable script

The script generator creates separate folders Tables, Views, Procedures in the specified directory. Creates scripts to create objects and stores in separate files in the appropriate folders. For tables, generation is performed taking into account dependencies (keys, indexes, etc.) and without. Creates a common file of similar scripts in the specified directory.

 //   Server myServer = new Server(@"myServ"); // Windows myServer.ConnectionContext.LoginSecure = true; //  myServer.ConnectionContext.Connect(); //  ,       string dir = Application.StartupPath +@"\"+ DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString(); Directory.CreateDirectory(dir); // ,   GenerateTableScript(myServer,dir); // ,   GenerateProceduresScript(myServer, dir); // ,   GenerateViewScript(myServer, dir); //  myServer.ConnectionContext.Disconnect(); 

Generating scripts for tables

  private static void GenerateTableScript(Server myServer, string path) { Directory.CreateDirectory(path + @"\Tables\"); string text = ""; string textWithDependencies = ""; //  ,     Scripter scripter = new Scripter(myServer); //    , "ZZZ" -    Database myAdventureWorks = myServer.Databases["ZZZ"]; //      ScriptingOptions scriptOptions = new ScriptingOptions(); //        //    Drop scriptOptions.ScriptDrops = false; //    If Not Exists scriptOptions.IncludeIfNotExists = false; //   foreach (Table myTable in myAdventureWorks.Tables) { // sql      StringCollection tableScripts = myTable.Script(scriptOptions); //    string newSql = ""; //  foreach (string script in tableScripts) { newSql = newSql + script; text = text + script; } //        File.WriteAllText(path + @"\Tables\" + myTable.Name + ".sql", newSql); //    scriptOptions.DriAllConstraints = true; scriptOptions.DriAllKeys = true; scriptOptions.DriDefaults = true; scriptOptions.DriForeignKeys = true; scriptOptions.DriIndexes = true; scriptOptions.DriNonClustered = true; scriptOptions.DriPrimaryKey = true; scriptOptions.DriUniqueKeys = true; tableScripts = myTable.Script(scriptOptions); newSql = ""; foreach (string script in tableScripts) { newSql = newSql + script; textWithDependencies = text + script; } //        File.WriteAllText(path + @"\Tables\" + myTable.Name + "_WithDependencies.sql", newSql); } //    File.WriteAllText(path + @"\" + "AllTable_WithDependencies.sql", text); File.WriteAllText(path + @"\" + "AllTable.sql", text); } 

Generating scripts for views

  private static void GenerateViewScript(Server myServer, string path) { Directory.CreateDirectory(path + @"\View\"); string text = ""; Scripter scripter = new Scripter(myServer); Database myAdventureWorks = myServer.Databases["ZZZ"]; ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.ScriptDrops = false; scriptOptions.IncludeIfNotExists = false; foreach (Microsoft.SqlServer.Management.Smo.View myView in myAdventureWorks.Views) { StringCollection ProcedureScripts = myView.Script(scriptOptions); ProcedureScripts = myView.Script(); string newSql = ""; foreach (string script in ProcedureScripts) { newSql = newSql + script; text = text + script; } File.WriteAllText(path + @"\Views\" + myView.Name + ".sql", newSql); } File.WriteAllText(path + @"\" + "AllView.sql", text); } 

Generation of scripts for procedures

  private static void GenerateProceduresScript(Server myServer, string path) { Directory.CreateDirectory(path + @"\Procedures\"); string text = ""; Scripter scripter = new Scripter(myServer); Database myAdventureWorks = myServer.Databases["ZZZ"]; ScriptingOptions scriptOptions = new ScriptingOptions(); scriptOptions.ScriptDrops = false; scriptOptions.IncludeIfNotExists = false; foreach (StoredProcedure myProcedure in myAdventureWorks.StoredProcedures) { StringCollection ProcedureScripts = myProcedure.Script(scriptOptions); ProcedureScripts = myProcedure.Script(); string newSql = ""; foreach (string script in ProcedureScripts) { newSql = newSql + script; text = text + script; } File.WriteAllText(path + @"\Procedures\" + myProcedure.Name + ".sql", newSql); } File.WriteAllText(path + @"\" + "AllProcedure.sql", text); } 

Conclusion

The described functionality can be useful for logging changes in the database structure, automatic filtering of unloaded objects by name. For example, a prefix in titles may indicate a separate direction in a project. Common and individual scripts can be added to SVN. You can write an automatic comparison of database objects and sending messages to the person in charge of the changes that have occurred.
Code can be found here .
The basis of the material was the article: Generate Scripts for database objects with SMO for SQL Server