4

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.

Any help would be appreciated.

Burt
  • 7,680
  • 18
  • 71
  • 127
  • Is there anything specific about the diagramming tools that come with SQL Server that make them insufficient for your needs? – Jacob Jan 15 '10 at 04:40
  • Any time a new database schema was generated we would have to create a new DB diagram. There is no way of saving the diagram as an image. The business analysts do not have access to SQL server (who the diagram is for)... – Burt Jan 15 '10 at 09:20

4 Answers4

2

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"

phatmanace
  • 4,671
  • 3
  • 24
  • 29
1

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

phatmanace
  • 4,671
  • 3
  • 24
  • 29
  • Unfortunately I need to be able to generate an image from the schema for the business analysts to use during their specifications. – Burt Jan 09 '10 at 23:31
1

My application dbscript generates Dia diagrams from a database schema.

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

devio
  • 36,858
  • 7
  • 80
  • 143
1

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?

phatmanace
  • 4,671
  • 3
  • 24
  • 29
  • It needs everything. Reverse engineer is a pain and we were looking to automate this step. – Burt Jan 12 '10 at 10:10
  • The database diagram looks promising but it would still need manual intervention to organise the tables which we are looking to avoid. – Burt Jan 12 '10 at 10:14