Reply to: SQL Modelling Tool

[vetran]

www.sparxsystems.com.au

may be of some use, I have just found it for something else but it seems pretty cool on the data side.

[Weltchy]

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

I think he's talking about the following

hibernate tools = Bed or Bedsheet
hand waving = a quick thomas the tank engine
Pojos = A variation on the Pogo theme, maybe a bouncing stick
TogetherJ = An obvious alias for Threaded's bed fellow, real name Klaus

[MarillionFan]

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

[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

[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.

[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.

[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..

[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]

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).

[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]

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]

Doesnt work already tried it.

[Weltchy]

"http://www.toadsoft.com/."

Toad couldnt do it either.

"If the SQL is ansi-92 compliant then using any of the standard query designers that come out of the box with t-sql or p-sql will do what your after no problems. Christ, you could drop it into ms access and get that kind of info, tho no proper database guru would ever mention Access and SQL in the same sentence."

Again. Somebody who doesnt know but offers a solution thinking theyre clever.

Bunch of Atws on here!

Actually, I think you'll find, as _V_ said, that SQL Query Designer will in fact take a raw SQL statement and drop it into a pretty relationship diagram at the top.

My question is, did you actually attempt to do this or just drop into 'Slag' mode automatically without giving it a reasonable attempt. As we appeared to be quite condescending, I will concede the latter part of the statement and apologise for it.

Now get off your lazy arsed contractor behind and do what they pay you to do and 'Work it out'

PS - Damn monday morning's always put me in a grouchy mood!!!!

[_V_]

I think you'll find that SQL Server client and VS.NET 2005 tools allow you to paste in the SQL and it will draw the pretty diagrams and it allows you to draw pretty diagrams and it will generate the SQL.

PS. Never call someone an AtW, that is a very nasty thing to do.

[MarillionFan]

"Not sure about these ones you speak of but I think he actually wants something to reverse engineer the query into a data model... can these do that? There are plenty of visual ones out there to design, but to go the other way is quite different..."

Only person who read this. Well done.