ConceptDevelopment.net

open source «stuff» ~ search, maps, database, etc

Database ~ Script SQL Server 2008 diagrams to a file

April 2009

View the source - it's only one file

This script is an update of ScriptDiagram2005. The 'old' version does work with SQL Server 2008, however this new version is slightly easier to use since it has no external dependencies. You should read the earlier doco for a brief history of the the script (and it's origins in Clay Beatty's SQL Server 2000 version).

Introduction

1. SQL Server allows you to draw diagrams of your schema
2. The diagrams are stored in a binary format in dbo.[sysdiagrams]
3. That data is backed-up/restored with the actual database, but there is NO SUPPORTED METHOD to save to a file. This is where ScriptDiagram2008 is used: to extract the data into text format.
4. Re-create diagram from the ScriptDiagram2008-generated script

ScriptDiagram2008 allows you to 'extract' the binary data from dbo.sysdiagrams into a text format that can be saved to disk and added to source control. It saves the data in the form of INSERT statements that re-create the diagram's binary data directly into dbo.[sysdiagrams]

How does it work

You can read about the previous version here - the 2008 update is limited to the removal of the Varbinarytovarchar() user-defined-function, replaced with a built-in (but undocumented?) SQL Server function sys.fn_varbintohexstr() (thanks to f).

1a. Create a new diagram

1b. Add tables to diagram

1c. Layout/view the diagram

2. sys.diagrams contains the diagram 'data'

3. Extract the 'data' with ScriptDiagram2008

EXEC Tool_ScriptDiagram2008 'AllTables' produces output in the Messages window, which is in fact an SQL script itself (ie. code generation!)

4. Re-create the diagram from 'script'

Messages window is the text-version of the diagram. You can save this file to disk, add it to source control, etc. You can also run the script back on the source database (to test it worked) - simply copy the script up into the query window and Execute it.

5. Script output indicates success

If the script failed to run successfully it will print an error message.

6. Refresh the Diagrams folder

7. The scripted diagram has been re-created!