Sunday, December 15, 2013

Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc

In this blog post I will highlight an overlooked feature in my SQL Server Compact Toolbox (Visual Studio add-in or standalone), the ability to create interactive relationship diagrams of SQL Server databases. And in combination with the free, open source DB>Doc for SQL Server, you have a full suite of documentation tools.

In order to create a diagram showing tables, columns and relationships for your SQL Server database, install the Toolbox.

Right click the root node of the Toolbox window, and select “Create SQL Server Database Graph (DGML)”:

image

Select the database that you want to diagram (the tool needs SELECT permission on the system views in the database)

image

You can also select to diagram only a subset of the tables in the database:

image

You will be prompted for a location to save the DGML file, enter this, and click OK, and Visual Studio will show the generated diagram in the DGML viewer, that comes with Visual Studio 2012 Pro or higher:

image

The viewer is quite advanced, from the Layout menu you can choose between various layouts of the dependency graph, and other advanced features to analyse the graph, for example. From the viewer you can Share as Image or XPS.
UPDATE: With the DGML Power Tools installed, you can also share a .SVG, which you can view in any web browser.

image

Click on a table, and select the down arrow button to expand the table into columns, and refer to the Legend also displayed (via the Legend menu item in the viewer):

image

More information on working with the graphs is available on MSDN:

Browse and Rearrange Dependency Graphs

Edit and Customize Dependency Graphs

To create HTML/XML based documentation of your database columns, you can use the open source DB>Doc tool. The main idea [of this tool] is to serve as a complementary tool to database diagrams, focusing on columns in tables and their meaning (common database diagrams focus to entity relationships).

I also use this tool for the documentation feature in the Toolbox for SQL Server Compact database files, which produces output similar to this:

image

Happy documenting! And feel free to provide feedback at https://sqlcetoolbox.codeplex.com/WorkItem/Create

4 comments:

David said...

I used this feature. It is totally excellent! Thank you!

BTW, I would like to point out you can use DGMLPowerTools VS2012 package to export to SVG, which is then easily viewable in browsers.

Wow Bagger said...

Hi!

I tried to use this for SQL Server CE (SDF) database, but didn't work.

The following command line tried:
c:\SQLDBDoc\sqldbdoc "Data Source=V:\Database\Adat.sdf;Password=123456789;Persist Security Info=True" v:\Database\Adatdoc.htm

I got the error message:
"Failed!
A network-related or instance-specific error occurred while establishing a conne
ction to SQL Server. The server was not found or was not accessible. Verify that
the instance name is correct and that SQL Server is configured to allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Serve
r/Instance Specified)"

How can I use it for SQL Server CE databases?

W.

ErikEJ said...

Wow: use the "Create Database Documentation" feature in the Toolbox!

Wow Bagger said...

Thank you!:)

So, I'm blind:DDDD