Generate schema script files for versioning (SQL Server)

Brief:

It's a windows application where we can mentioned SQL server connection details. It has logic which creates folders for Tables, Stored Procs, Triggers, Views, etc and generate Create script for all the objects in SQL server database. These script files are text base, so we can easily commit it in Repository. It becomes very easy to find any schema related changes through SVN diff command.

Need:

We all know about importance of code backup. In fact importance for Version control system. We follow the guidelines and make sure that the code is committed in repository. It helps a lot in release management. But we miss a very important item in versioning. Database versioning along with QA certified code. Majorly we are concerned about the Schema of the database. At many times don't need data. To sort out this issue, a small tool is created. It is specially created for MS SQL Server. It's a windows application where you need to specify instance name, Database Name, Username, Password and output location where it will create all the SQL script files of all the objects found in respective database.

How It Works:

1. To communicate with SQL server we need below namespace to be added in the form.

using Microsoft.SqlServer.

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

2. Create object of ServerConnection()

ServerConnection con = new ServerConnection(txtInstanceName.Text.Trim(), txtUsername.Text.Trim(), txtPassword.Text.Trim());

3. Use Connect() to connect to the server.

con.Connect();

4. Initialize Server object and check if database exists and get the instance.

srv.Databases.Contains(txtDatabase.Text.Trim());
Database db = srv.Databases[txtDatabase.Text.Trim()];

5. Traverse through all tables and create script through Script(). Write this string in a file with extension as .sql.

foreach (Microsoft.SqlServer.Management.Smo.Table tab in db.Tables)
tab.Script();

6. Also check if each table has any triggers.

Microsoft.SqlServer.Management.Smo.Trigger trig in tab.Triggers

7. For all Stored Procs use.

foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in db.StoredProcedures)

8. For each User Defined functions.

foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction fun in db.UserDefinedFunctions)

9. For each Views.

foreach (Microsoft.SqlServer.Management.Smo.View view in db.Views)

10. For each User defined user table types.

foreach (Microsoft.SqlServer.Management.Smo.UserDefinedTableType utt in db.UserDefinedTableTypes)

11. For each user defined data type.

foreach (Microsoft.SqlServer.Management.Smo.UserDefinedDataType udt in db.UserDefinedDataTypes)

12. For each full text catalog.

foreach (Microsoft.SqlServer.Management.Smo.FullTextCatalog ftc in db.FullTextCatalogs)

These generated files are text based. It can be easy added in repository and maintain versions along with application source code.