Automate SQL Server DB Schema Diagram Generation

As part of our DB change control process one of the tasks is to reverse engineer the changes into our schema diagram, using Visio. This is a little tedious and we were looking at a way to automate this.

We already have a CI and Release to test process that generates the latest DB all we need is some application that will analyse the database and when required generate a diagram from it.开发者_如何学C

Any help would be appreciated.

ok - third time lucky.

I don't know of any tool that can do this in one step - but I don't think that any of them are going to do what you want (except for probably some really high end tools).

Thus, I think that your best bet is to use one of the aforementioned script like dbschema.pl and send the output to something like sql2diagram

This should be simple to script up - and the email the output to whomever needs to see it. Since you have the text output at the intermediate stage, you can highlight diffs as well bby preserving multiple copies of the output.

Can you clarify what you mean by "Organize the tables"

have a look at DBSchema - this is quite a good tool. it dumps your DDL to files each day, you can then see what's changed day on day by a simple text comparison. Clearly, implicitly, you also get something that you could use to regenerate the structure of your DB as well.

If you insist that people check DDL into CVS, then you can also use it to spot changes that people have made ad-hoc to the db structure without checking into source control.

Hope this helps, Ace

My application dbscript generates Dia diagrams from a database schema.

Since Dia exports various formats, it should be no problem to import into Visio.

ah - in lieu of the comment added to my original answer - perhaps there are some other options

• Visio "reverse engineer database" - and run this from a macro to run automatically
• something like this? Google DB Tool

does your diagram need to show the whole database, or just the changes?