SQL Modelling Tool

[MarillionFan]

Doesnt work already tried it.

[f_eriksen]

As others already have stated, going the other way around and visually generate DML statements are quite common (but I cant be bothered to use them..).

However, if I understand you correctly, what you have is a bunch of queries (I'm assuming DML statements), without actual DDL's for the underlying physical model. Correct? I think most tools would have a hard time reverse engineering a physical model based on selects, joins etc, as there is next to no way of knowing the definition of datatypes etc (with the exception of possible function calls etc that might give you clues..).

Having said that, I did see a tool that generated flow charts (I know, not what you were asking for..) for complex sqls. Cant remember the name though, not very good in my opinion and I ditched it.

There is a reason why it's so common on db related forums and newsgroups to post the required DDL's along with the query one has issues with

Sorry I cant help any more then that..

[MarillionFan]

Tried about six or seven so far and they're all bloody useless. All insist on the code for the Create Table which is not what I am after.

All I want is to produce diagrams based on the select statement. Is this too difficult to ask.

The view should contain the table name and only show joins where they exist. Obviously I cannot see the schemas for the individual tables only columns specifically referenced.

Having had a look around I reckon there is a need for an Online Tool that you paste in a SQL statement and it produces a graphical Relationship Model that you can right mouse click and download. I have a quiet week, think I'll knock one up in .NET.

I have about 50 of these things to do for a review document.

[f_eriksen]

I understand the frustration,

And will be quite impressed if you manage to build in effect a .net based sql parser

- Tried a few times for various documentation needs, but considering the complexities of "serious" sql's, nested subqueries, table based functions, multi-level case statements etc etc...

Trust me, if at all possible, just ask for the DDL's

Beyond that, best of luck and please let me know how you solved it (being a die hard db guy, I'd love to see new tools and ways of documenting queries).

[Weltchy]

Suppose I should have figured that since you don't have the source tables in the database, Query Designer would complain that the tables / fields dont exist.

Must admit, that probably does complicate it a bit. You could try having a look at some of the red gate stuff over at www.redgate.com. I use this when I need to make updates to sql databases, as it picks up differences in database schemas and will rebuild them for you, etc. Whilst I dont know whether it does what your after, it would be a good place to look.

[f_eriksen]

red-gate makes excellent tools (sql compare and datacompare are both products I have used extensively for various change mgmt routines), but I fear in this case they will have nothing to offer either.

As I said earlier, the problem lies in that there is ismply not enough information generally within a select statement to reverse engineer the underlying structure.

I guess, if the statements are not too complex, and qualified names are used in the column list (alias|table name|schema name.column name), it would not be impossible to parse out the columns and simple generate a dummy table using a common data type. But that would require some pretty simple sql, nicely coded, etc, etc..

[MarillionFan]

No I disagree.

Effectively within say Crystal Reports the modelling tool only shows the columns/joins you have created. Obviously it cannot reverse engineer.

I am documenting a large reporting process undertaken over four linked servers. Each part of the business owns a different piece and it is badly written. I am aiming at management and need to show graphically where there is overlap. So i have a process with 50 views & stored procedures.

I need a large graphical document with a flow for all 50 views.

I am using sp_helptext to get the views as they are all remote and I am not allowed access to the linked servers!!! sp_helptext does not give me the structure of the table, plus as I am starting with the view first I dont know which tables I need.

So Im doing it by hand. Some of the views reference twenty tables and have multiple joins using either ON or within the WHERE clause.

How cool to paste it into something and to have a 'fuzzy' model.

[SallyAnne]

I have to say MF, I have to do this kind of thing all of the time, and I sit and draw them out by hand on bits of paper.

A tool would make it easier, but drawing it out always helps me to understand the schema(s).

There probably is a market for this tool though. Let me know if you build it.

[threaded]

I had a similar problem some time ago. I used some hibernate tools and hand waving to build a load of pojos and then got TogetherJ to draw a diagram of the pojos. It was a one-off for documentation and wasn't conducive to automation, but it got the job done.

HTH

[MarillionFan]

Can anyone tell me what the hell threaded is talking about?