Click here to Skip to main content
13,351,954 members (87,921 online)
Click here to Skip to main content
Add your own
alternative version


36 bookmarked
Posted 9 Oct 2009

Automatic Script SQL Server 2005 Objects and Commit under Subversion

Rate this:
Please Sign up or sign in to vote.
Automatic script SQL Server 2005 objects and commit under Subversion


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.


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:

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
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:



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! :)


  • 09 October 2009 - First release 


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


About the Author

Ferreri Gabriele (Megasoft78)
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:

and my first Android game (Fifteen Puzzle X):

You may also be interested in...


Comments and Discussions

QuestionAutomatic Script SQL Server 2005 Objects and Commit under Subversion Pin
Member 1248180414-Jun-17 3:02
memberMember 1248180414-Jun-17 3:02 
GeneralHow to use this code ? Pin
maboharb18-Jul-12 7:54
membermaboharb18-Jul-12 7:54 
GeneralRe: How to use this code ? Pin
Ferreri Gabriele (Megasoft78)18-Jul-12 9:24
memberFerreri Gabriele (Megasoft78)18-Jul-12 9:24 
QuestionRequest to provide encoding as argument Pin
Chris Rokusek10-Apr-12 12:56
memberChris Rokusek10-Apr-12 12:56 
AnswerRe: Request to provide encoding as argument Pin
Ferreri Gabriele (Megasoft78)10-Apr-12 20:21
memberFerreri Gabriele (Megasoft78)10-Apr-12 20:21 
QuestionGreat stuff, request to control use of timestamp file Pin
Chris Rokusek10-Apr-12 10:26
memberChris Rokusek10-Apr-12 10:26 
QuestionServer name with embedded slash Pin
Chris Rokusek9-Apr-12 12:24
memberChris Rokusek9-Apr-12 12:24 
AnswerRe: Server name with embedded slash Pin
WernerCD1-May-12 8:02
memberWernerCD1-May-12 8:02 
QuestionSQL 2008? Pin
ScottPiersallJax17-Oct-11 5:13
memberScottPiersallJax17-Oct-11 5:13 
AnswerRe: SQL 2008? Pin
Ferreri Gabriele (Megasoft78)17-Oct-11 6:55
memberFerreri Gabriele (Megasoft78)17-Oct-11 6:55 
QuestionSuggestion Pin
Vijay Chandra Sekhar Parepalli20-Sep-11 19:48
memberVijay Chandra Sekhar Parepalli20-Sep-11 19:48 
AnswerRe: Suggestion Pin
Ferreri Gabriele (Megasoft78)20-Sep-11 23:34
memberFerreri Gabriele (Megasoft78)20-Sep-11 23:34 
I'm not sure but I think SQL Server 2005 doesn't hold that information about who made that change.
Unless I'm wrong and that information is in SQL Server 2005 I can't see how to do that.
Actually in our company we are planning to use a product from RedGate called SQL Source Control that it should make everything easier but we haven't test it yet.

Best Regards,
Your mind is like a parachute. It works best when open.

QuestionSQL Scripter do not generating GO statement while writing to file.... Pin
Prasanth.PV18-Aug-11 1:50
memberPrasanth.PV18-Aug-11 1:50 
AnswerRe: SQL Scripter do not generating GO statement while writing to file.... Pin
Ferreri Gabriele (Megasoft78)18-Aug-11 3:55
memberFerreri Gabriele (Megasoft78)18-Aug-11 3:55 
GeneralRe: SQL Scripter do not generating GO statement while writing to file.... Pin
Prasanth.PV25-Aug-11 8:08
memberPrasanth.PV25-Aug-11 8:08 
GeneralAdded functionality so if arg[1] is *, loops through all databases Pin
TamusRoyce14-Jan-11 10:38
memberTamusRoyce14-Jan-11 10:38 
GeneralRe: Added functionality so if arg[1] is *, loops through all databases Pin
Ferreri Gabriele (Megasoft78)15-Jan-11 21:48
memberFerreri Gabriele (Megasoft78)15-Jan-11 21:48 
QuestionHow to connect to SQLServer db at Remote machines (with UserId and Password) . [modified] Pin
Rami.P15-Nov-10 0:35
memberRami.P15-Nov-10 0:35 
AnswerRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Ferreri Gabriele (Megasoft78)15-Nov-10 1:11
memberFerreri Gabriele (Megasoft78)15-Nov-10 1:11 
GeneralRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Rami.P15-Nov-10 20:13
memberRami.P15-Nov-10 20:13 
GeneralRe: How to connect to SQLServer db at Remote machines (with UserId and Password) . Pin
Ferreri Gabriele (Megasoft78)15-Nov-10 23:16
memberFerreri Gabriele (Megasoft78)15-Nov-10 23:16 
GeneralGreat script - thanks Pin
ger0nim07-May-10 23:49
memberger0nim07-May-10 23:49 
GeneralMore good news Scripting Data Pin
Gaben4-Dec-09 10:31
memberGaben4-Dec-09 10:31 
GeneralRe: More good news Scripting Data Pin
Ferreri Gabriele (Megasoft78)4-Dec-09 20:41
memberFerreri Gabriele (Megasoft78)4-Dec-09 20:41 
GeneralIsSystemObject Speed Issue Pin
Gaben3-Dec-09 12:40
memberGaben3-Dec-09 12:40 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 10 Oct 2009
Article Copyright 2009 by Ferreri Gabriele (Megasoft78)
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid