So... I've ended up in a project, which by far is the worst quality POS I've seen in a long while. It is basically a Visual Basic .NET windows client application used for various administrative tasks and print outs. It is using inline SQL (concatenated sql-strings of course) and stored procedures all over the place. All reports have their own embedded SQL. On top of this it is joining in tables from four different databases, two of which is clearly some reverse engineered attempt to integrate two standard systems not meant to be integrated at all. It's even updating data in these other systems.

OK. With some sort of hope of a complete rewrite in the near future I'm trying to make sense of this system. My plan is break it down into use cases, and document these from a database/integration standpoint.

Is there, some tool, that's using a SQL-profiler data dump or similar to "reverse engineer" a database diagram. I'd like to see some sort of graphical representation or anything at all to straighten out this spaghetti...

(Or any other ideas or help on how to approach this would be helpful!)

  • What have you tried so far? Tools? SSMS, ERWin, Visio? Do you have full access to the code and SQL Server databases? – SS_DBA Sep 20 '16 at 11:57
  • Well, I've got SSMS, complete sa access to the database, complete access to source-code etc. Since there's no real constraints in the main database and the joins are between various databases, the built in diagram tool isn't much help. I'd like some sort of tool to display what columns are joined to which columns and where. I guess I'm after an easy way to display a very complex structure.... – Robert Bengtsson Sep 20 '16 at 12:03
  • 1
    Take a look at SQL Sentry Plan Explorer, not only is it going to allow you to tune those poor running queries if you run a query in the tool, it will show you join diagram of that query with all tables and columns used. Plan Explorer. Just as FYI, question of these type generally tends to be closed as it is asking for a tool and is very subjective. – Vladimir Oselsky Sep 20 '16 at 12:19
  • OK, thanks. I've got a pretty good idea how to get my head around a more or less organized database. This, on the other hand, is ten years of ad-hoc-organically-grown-legacy-mess that undoubtedly has some sort of structure to it. It's just that is impossible to get the bigger picture without spending weeks on it. (The same actual entity are called item, products or article depending on what database you're looking at) I'm just asking for advise on how to approach this. I'm sure there are others out there with experience! – Robert Bengtsson Sep 20 '16 at 13:03

Try Dataedo - it's a database documentation tool that I think can help in your case. You will be able to import multiple database schemas into one repository, then further split them into smaller pieces called modules. You can describe with rich text and create ER diagram for each. You can define missing FKs and use them for the diagrams. You are able to describe every data and program element (table, column, function, parameter etc.) and export such a documentation to PDF document or HTML. Maybe this will straighten out your spaghetti ;)

I'm the product manager of Dataedo.

| improve this answer | |
  • Thanks for your answer, the politics in this projects is almost as bad as the code (That seems to correlate often doesn't it?) so the whole thing is paused at the moment. After quick glance at your website it seems like Dataedo might be very useful in our scenario. Honestly I'm hoping that I never have to look return to this project again, but if (or more like when) I'll take Dataedo for a spin! – Robert Bengtsson Sep 27 '16 at 6:19
  • Hi Robert, you're welcome. Don't worry - everyone had a project like this - messy code, requirements and no support. Check the tool when the time comes. Good luck with the project. – Bad Pitt Sep 29 '16 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.