Scripting SQL Server diagrams to files for source control

We have recently started storing our SQL Server 2005 database schema and test data in a SubVersion repository but have an issue that diagrams are not supported by any of the commercial products out there that we’ve found.

I came across some code on CodeProject by Craig Dunn at http://www.codeproject.com/KB/database/ScriptDiagram2005.aspx. Which in turn was based on some SQL Server 2000 code by Clay Beatty.

However I found some issues with this, the first was that the output SQL script was piped to the SQL messages (e.g. PRINT rather than SELECT) which I couldn’t work out how to get at using .NET.  If anyone knows how to get at the messages I would be most interested!  The second issue was that it only worked for a single diagram per-call.

Thus I’ve tweaked it to output a two column table with the first column storing the diagram name and the second column the SQL script to generate the diagram.  The Stored Procedure can take a @name parameter if you wish to script a single diagram in which case you’ll only get one row in the resultset.

Included is a PowerShell script which then puts each row in to its own file which should be somewhat suitable for inclusion in Source Control.  I would like to say this is my first ever PowerShell script and all of this was done in a hurry.  Edit the PowerShell script to specify your database connection string and the path you want the SQL scripts placed in.  To run the PowerShell script you must allow unsigned code (http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx) e.g.

 Set-ExecutionPolicy RemoteSigned

Code is at: http://www.provenworks.com/Blogs/Joel/ScriptDBDiagrams.zip 

If you find it useful please comment below!