Click here to Skip to main content
15,861,168 members
Articles / Database Development / SQL Server

Automatic Script SQL Server 2005 Objects and Commit under Subversion

Rate me:
Please Sign up or sign in to vote.
5.00/5 (11 votes)
9 Oct 2009CPOL4 min read 90K   1.3K   37   33
Automatic script SQL Server 2005 objects and commit under Subversion
Image 1

Introduction

Recently we changed our source control from Sourcesafe (I know it's prehistoric!) to SubVersion and except for some issues (mainly training ones), we are pretty happy with it. I really like the branching and merging functionalities that give us a big help in our deployment process.

Unfortunately this helps us only for the C# code but because our application is Database-Centric and a lot of our business logic is in tables, views and stored procedures, effectively everything is not under source control, and even more important, we have no history of changes.

The first solution I suggested to solve this issue was to buy Microsoft Visual Studio 2008 Database Edition that effectively give us a very powerful environment to handle the database changes like C# project and have a lot of functionalities like database reverse engineering, schema and data comparison. The first issue with this solution is that Microsoft Visual Studio 2008 Database Edition (VSDE) is strongly linked to Team Foundation Server (TFS), and TFS is not really cheat.

The second issue is that the developers need to change the way they work with databases. In fact, instead of opening SQL Management Studio and just changing the database directly, with VSDE, they will need to create the script to alter the database and then run it against a local temporary database. To be honest, this seems to me the best way to work but it could be an issue for some developers.

The second solution I suggested to solve the issue was to periodically script the database objects and commit these scripts into SubVersion.

This solution will give me the history about database changes without changing the way we work with databases. This process can be automatic and I can setup on the server machine, using scheduler tasks, to run periodically every 30 minutes. If you like to have more granularity on your history, you should setup the scheduler to run every 15 minutes or less. Consider that if the database has not changes, the process is not going to commit anything.

This tutorial will explain how to create this automatic process.

Background

When I start to investigate on this issue, I was looking for a command line tool that will give me the ability to script all the database objects. I found a lot of nice open source projects that do this in different ways:

Unfortunately scripting the entire database every time for a big project is very slow and for this reason, I tried to create a custom console application that using Microsoft.SqlServer.Management.Smo scripts only the differences between the current run and the previous one.

The Implementation

The console application is divided in two classes:

  • Program.cs: Entry point of the application that handles the input parameters and calls the helper class to script the database objects.
  • ScripterHelper.cs: The helper class that uses Microsoft.SqlServer.Management.Smo scripts the database objects.

The helper class stores the last modified object date in a text file named <Server>-<Database Name>.txt under the application's path. This gives the ability to the class for the next run to detect the changed objects and script just the differences. Embedded into the helper class I implemented a logic to add/delete script files for new/deleted objects in the database.

In this way, using a simple batch file, it is easy to automatically commit the changes in SubVersion.

The most interesting part of the code is how to use Microsoft.SqlServer.Management.Smo to script the database objects.

The class used to do that is Scripter:

C#
Server server = new Server("(local)"); // Server name of the database to script
Database db = server.Databases["AdventureWorks"] 	// Database name of the 
						// database to script

Scripter scripter = new Scripter(); // Class script the database objects
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = true;

foreach (Table table in db.Tables) // Script all tables
{
   if (!table.IsSystemObject) // Skip the system objects
   {
      scripter.Options.FileName = 
	Path.Combine("C:\Scripts", table.Name + ".sql"); // Filename of the script
      scripter.Script(new Urn[] { table.Urn }); // Object to script
   }
}

The integration with SubVersion is done with a small batch file:

rem Script the database objects AdventureWorks under the 
	local SQL Server into the folder C:\Scripts
SQLScripter.exe (local) AdventureWorks "C:\Scripts"

rem Change current Folder
C:\Scripts
rem Use for command to detect the file status and eventually 
	add or delete the files from svn
for /f "tokens=2*" %%i in ('svn status ^| find "?"') do svn add %%i
for /f "tokens=2*" %%i in ('svn status ^| find "!"') do svn delete %%i

rem Commit the changes in svn
svn commit -m "Automatic commit" "C:\Scripts"

Using the Code

The console application accepts the following parameters:

  • Server (Host of the database to script)
  • Database Name (Database name of the database to script)
  • Scripts Path (Path where the application will store the scripts)
  • Object Types (The filter parameter to restrict the object types will be scripted: t=Tables,v=Views, s=Stored Procedures, a=User Defined Aggregates, f=User Defined Functions, Empty=All)

The following screenshots show the result structure created and the tables folder for AdventureWorks database:

Screenshot2.jpg

Screenshot3.jpg

Points of Interest

This solution can be improved to script the data of the lookup tables and put it under source control. Another idea could be to use it to generate a deployment script and simplify the deployment process.

Any suggestions are welcome! :)

History

  • 09 October 2009 - First release 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) sparesFinder
Italy Italy
I'm an Italian Software Developer from about 15 years.
I worked a long time in south Italy (where I was born) and after 2 years in Milan and an year in UK, I'm working remotely from Italy as Senior ASP.NET C# Developer using ASP.NET Ajax technology for a UK company.

Check out my personal blog:
http://techcookies.net/

and my first Android game (Fifteen Puzzle X):
https://play.google.com/store/apps/details?id=it.megasoft78.fifteenpuzzlex

Comments and Discussions

 
QuestionReplication Scripts Automation Pin
Member 1369343923-Feb-18 9:39
Member 1369343923-Feb-18 9:39 
QuestionAutomatic Script SQL Server 2005 Objects and Commit under Subversion Pin
Member 1248180414-Jun-17 2:02
Member 1248180414-Jun-17 2:02 
GeneralHow to use this code ? Pin
maboharb18-Jul-12 6:54
maboharb18-Jul-12 6:54 
GeneralRe: How to use this code ? Pin
Ferreri Gabriele (Megasoft78)18-Jul-12 8:24
Ferreri Gabriele (Megasoft78)18-Jul-12 8:24 
QuestionRequest to provide encoding as argument Pin
Chris Rokusek10-Apr-12 11:56
Chris Rokusek10-Apr-12 11:56 
AnswerRe: Request to provide encoding as argument Pin
Ferreri Gabriele (Megasoft78)10-Apr-12 19:21
Ferreri Gabriele (Megasoft78)10-Apr-12 19:21 
QuestionGreat stuff, request to control use of timestamp file Pin
Chris Rokusek10-Apr-12 9:26
Chris Rokusek10-Apr-12 9:26 
QuestionServer name with embedded slash Pin
Chris Rokusek9-Apr-12 11:24
Chris Rokusek9-Apr-12 11:24 
AnswerRe: Server name with embedded slash Pin
WernerCD1-May-12 7:02
WernerCD1-May-12 7:02 
QuestionSQL 2008? Pin
ScottPiersallJax17-Oct-11 4:13
ScottPiersallJax17-Oct-11 4:13 
AnswerRe: SQL 2008? Pin
Ferreri Gabriele (Megasoft78)17-Oct-11 5:55
Ferreri Gabriele (Megasoft78)17-Oct-11 5:55 
QuestionSuggestion Pin
VC Sekhar Parepalli20-Sep-11 18:48
VC Sekhar Parepalli20-Sep-11 18:48 
AnswerRe: Suggestion Pin
Ferreri Gabriele (Megasoft78)20-Sep-11 22:34
Ferreri Gabriele (Megasoft78)20-Sep-11 22:34 
QuestionSQL Scripter do not generating GO statement while writing to file.... Pin
prasanthpv18-Aug-11 0:50
prasanthpv18-Aug-11 0:50 
AnswerRe: SQL Scripter do not generating GO statement while writing to file.... Pin
Ferreri Gabriele (Megasoft78)18-Aug-11 2:55
Ferreri Gabriele (Megasoft78)18-Aug-11 2:55 
GeneralRe: SQL Scripter do not generating GO statement while writing to file.... Pin
prasanthpv25-Aug-11 7:08
prasanthpv25-Aug-11 7:08 
GeneralAdded functionality so if arg[1] is *, loops through all databases Pin
TamusRoyce14-Jan-11 9:38
TamusRoyce14-Jan-11 9:38 
GeneralRe: Added functionality so if arg[1] is *, loops through all databases Pin
Ferreri Gabriele (Megasoft78)15-Jan-11 20:48
Ferreri Gabriele (Megasoft78)15-Jan-11 20:48 
QuestionHow to connect to SQLServer db at Remote machines (with UserId and Password) . [modified] Pin
Rami.P14-Nov-10 23:35
Rami.P14-Nov-10 23:35 
AnswerRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Ferreri Gabriele (Megasoft78)15-Nov-10 0:11
Ferreri Gabriele (Megasoft78)15-Nov-10 0:11 
GeneralRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Rami.P15-Nov-10 19:13
Rami.P15-Nov-10 19:13 
GeneralRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Ferreri Gabriele (Megasoft78)15-Nov-10 22:16
Ferreri Gabriele (Megasoft78)15-Nov-10 22:16 
GeneralGreat script - thanks Pin
ger0nim07-May-10 22:49
ger0nim07-May-10 22:49 
GeneralMore good news Scripting Data Pin
Gaben4-Dec-09 9:31
Gaben4-Dec-09 9:31 
GeneralRe: More good news Scripting Data Pin
Ferreri Gabriele (Megasoft78)4-Dec-09 19:41
Ferreri Gabriele (Megasoft78)4-Dec-09 19:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.