Introduction
This small console application exports information about a database from SQL Server to an XML file. This can come in handy when you have a version control system like SVN or GIT that manages all of your source code but lacks any way to track the changes you make to your tables in SQL Server.
Whenever you make a database change, simply run this code, and check-in the XML file that it produces, or just make that part of your MS Build task so it happens automatically with every build you make on your continuous integration server.
This utility can also come in handy to ensure that your development environment, staging environment, and production environment all have the same SQL tables in place. Just run it once for each environment, and diff the files to see what's missing. Since the XML file contains all the T-SQL CREATE scripts you'll need, it's easy to cut and paste into the SQL Management Studio Express to add whatever is missing in your other environments.
The example is also intended as a demonstration of SQL Management Objects and LINQ to XML.
Background
There are plenty of [expensive] tools to manage your SQL databases and to synchronize them between environments, but if you need a simple tool for a smaller project, something like this might be just what you need, and since it's all just plain C# code, you can easily build this into your build or deployment projects. With this in place, you'll now have a record at every step along the way as to the structure of your database and when changes were made to fields, indexes, and to foreign key constraints. Your SVN logs will show both table changes AND code changes, making it easier to blame whoever messed things up see what changed when.
The code does not attempt to copy every aspect of the database to the XML file; you can do that if you want by extending it, but for now, I just needed a quick tool to keep database fields, indexes, and constraints in sync between my various environments, and SQL MO was the easy way to achieve that.
Using the code
Compile the code and run the executable from a command line, passing it three arguments:
DatabaseExport <databasename> "<connection string>" [<output file>]
For example, if Exceptions is the name of a database on the local SQL instance, you could export it to XML using:
DatabaseExport Exceptions ".;Integrated Security=True;Pooling=False;"
The result might look something like this ...
="1.0"="utf-8"="yes"
<database version="1.0" name="exceptions">
<table name="ExceptionRecord">
<body name="ExceptionRecord"><![CDATA[</body>
<index name="PK_Exception"><![CDATA[</index>
</table>
<table name="ExceptionRecordData">
<body name="ExceptionRecordData"><![CDATA[</body>
<index name="PK_ExceptionData"><![CDATA[</index>
</table>
</database>
That's actually my Exceptions database into which I log every exception thrown by any of my solutions, more about that some other time maybe ...
As you can see, each table becomes its own element in the XML file, and the script to create that table is there as a CDATA section. Each index and foreign key constraint also gets a section, with the script to create it.
The code to do all this is remarkably simple. SQL Management Objects gives us a way to discover all the Tables, Indexes, and other objects under a database. For example, here, we ask for a database with a specific name, and then ask for the collection of tables under it:
Database db1 = server1.Databases[databaseName];
TableCollection tc1 = db1.Tables;
Once we have a collection of database objects, we can use the magic of LINQ to query them and construct the XML elements from them. This simple method takes a SQL MO collection and a generic parameter T, specifying the type we expect to find in that collection. Once we've cast the elements of the collection to type T, we can construct a query over them, and from that, it's easy to generate the IEnumerable<XElement> sequence we want.
Note how we put generic constraints on the type T to say that it must be a NamedSmoObject and it must support the IScriptable interface which is the one used to generate the T-SQL script for that object.
private static IEnumerable<XElement> XElementsFromCollection<T>(SmoCollectionBase collection)
where T : NamedSmoObject, IScriptable
{
var query =
from namedObject in collection.Cast<T>()
orderby namedObject.Name
select new XElement(typeof(T).Name.ToLower(),
new XAttribute("name", namedObject.Name), EmitScript(namedObject));
return query;
}
With that method in place, the following single line of code is able to do 99% of the work. It constructs an XML 'table' element with a name attribute equal to the name of the SQL table, and then within that, a body element containing a CDATA section for the script to create that table, and then a list of indexes and foreign keys each with CDATA sections for their own creation scripts.
databaseElement.Add(new XElement("table",
new XAttribute("name", table.Name),
new XElement("body", new XAttribute("name", table.Name), EmitScript(table)),
XElementsFromCollection<Index>(table.Indexes),
XElementsFromCollection<ForeignKey>(table.ForeignKeys)
All that remains is to wrap that up in an XML document and to write it out to a file.
XDocument root = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
new XComment("Dump of database for " + server1.Name + ", database " + databaseName),
databaseElement = new XElement("database",
new XAttribute("version", "1.0"),
new XAttribute("name", databaseName)
));
Points of interest
With LINQ to XML, what would have taken some convoluted foreach statement spanning 10 lines of code is now accomplished in a single line of code. For example, generating a string with a list of all the database names separated by commas is achieved thus:
string databases = "[" + string.Join(",",
server1.Databases.Cast<Database>().Select(d => d.Name).ToArray()) + "]";
Follow on article anyone?
If anyone is interested, I may do a follow on article showing how to take this XML file and compare it back against another instance of the database using a simple GUI where you can click to add any missing tables, indexes, or foreign key constraints. That's what I'm working on now to complete this 'diversion' and then, once I no longer need to worry about keeping all my database tables in sync across two development machines and one continuous integration server, I can get back to writing some 'useful' code instead.
History
First version.
