- Thanks received
- 767
-
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
SQL Modelling Tool
- Thread starter MarillionFan
- Start date
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..
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..
- Thanks received
- 767
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.
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.
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).
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).
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.
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.
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..
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..
- Thanks received
- 767
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.
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.
- Thanks received
- 10
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.
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.
- Thanks received
- 38
- Thanks received
- 767
