Author |
Message |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
FR: Query/Statement Visualizer |
|
Would it be possible or feasible to have a tool inside of SQL Assistant that you could click "Visualize" on the selected query and it could give some sort of visual representation of the joins?
(I stole this from a Stack Overflow post asking for such if a tool exists) but there's not really anything out there that I've found.
|
|
Thu Nov 10, 2016 3:09 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
Looks like Visual Studio Community will do it, but at 6GB installed just for something to visualize queries is a bit heavy.
|
|
Thu Nov 10, 2016 3:31 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
I used DbSchema in the past (and still use it for design), a very good designer, knows a quite a few different database types. It's a java application, and as such, it sucks at a number of things (most importantly not being able to cooperate with SA). It had a decent visual query builder. But I think you'd need it the other way around, right? I guess most of the apps even remotely touching this topic aren't doing it reverse. I recall, Visual Studio having something, and I have it on board due to SSIS anyway, but I can imagine installing 6gigs (and a bunch of unwanted features) to be an overkill.
Didn't SSMS had something similar? Though it might require the query built visually there too.
By the way, why is this post closed?
|
|
Thu Nov 10, 2016 4:08 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
I used DbSchema in the past (and still use it for design), a very good designer, knows a quite a few different database types. It's a java application, and as such, it sucks at a number of things (most importantly not being able to cooperate with SA). It had a decent visual query builder. But I think you'd need it the other way around, right? I guess most of the apps even remotely touching this topic aren't doing it reverse. I recall, Visual Studio having something, and I have it on board due to SSIS anyway, but I can imagine installing 6gigs (and a bunch of unwanted features) to be an overkill.
Didn't SSMS had something similar? Though it might require the query built visually there too.
By the way, why is this post closed? |
SSMS does have a query designer, but when you're dealing with #tempTable or @tempTable it doesn't do well there.
I did find http://www.snowflakejoins.com that seems OK for statements under 800 char. It's based on RevJ http://revj.sourceforge.net/ ... I might try and get RevJ going on my machine to avoid the 800 char limit.
As far as any tools that do it, I'd like to be able to paste in my statement and have it visualize the joins. I could go reverse on it, but it's a bonus if it'll parse what I've got to confirm I'm on target. This is only a problem for me when I get too many joins going or start doing self joins I get crosseyed.
|
|
Thu Nov 10, 2016 4:15 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Believe it or not we are planning Code Visualizer for the next maintenance release 9.1.
I cannot give you more details at this time, it's still pretty much in a research stage.
|
|
Thu Nov 10, 2016 5:01 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
Believe it or not we are planning Code Visualizer for the next maintenance release 9.1.
I cannot give you more details at this time, it's still pretty much in a research stage. |
Sweet! Looking forward to it.
|
|
Thu Nov 10, 2016 5:03 pm |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Okay, I knew there was something I missed and it stalked all the time in my subconscious :) Though it was not why I asked for it here, and it does not show the columns (only primary keys) on the diagram, Sentry Plan Explorer might be what you're looking for. It does show you join for queries (even complex ones). Never checked their validity though. How about you give it a test ride until there's a working Code Visualizer (eagerly awaiting it, btw)?
![](https://s26.postimg.org/vm7il429l/screenshot_23.png)
|
|
Thu Nov 10, 2016 5:16 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
How do you get that to show up?
*edit: got it.
|
|
Thu Nov 10, 2016 5:52 pm |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
I was looking at your plan explorer screenshot again. Why do you have so many CASE statements that seemingly evaluate the same thing over and over?
|
|
Fri Nov 11, 2016 10:49 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
![](templates/GreenTech/images/quote_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
I was looking at your plan explorer screenshot again. Why do you have so many CASE statements that seemingly evaluate the same thing over and over? |
The rest of the statement cannot be seen :) but from what's visible you can tell that it returns three columns. Those are all different but whether they yield a value or a NULL depends on the state of that DI column and you cannot return three columns from a single case statement. It's a result of bad planning. Someone hates NULLs (I don't think that person grasps the concept :) ) and instead of returning NULL in certain columns, 0s were put there and now I have to check values in other columns to see if that's a valid value or bs.
|
|
Fri Nov 11, 2016 10:56 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
A new tool called Code Visualizer has been added to version 9.2. It's not exactly like what's pictured in previous posts. Nevertheless, it aims at helping to visualize the code logic and document it as well.
|
|
Sat Jul 15, 2017 8:10 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Great! May I request a hotkey for it in case there isn't one yet? I can see myself wearing it all the way down to the hull.
|
|
Sat Jul 15, 2017 9:52 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
Thank you. I will submit an enhancement request to add a configurable hot key.
Just in case, if you have two monitors or a large one, you can set it to show up in a floating window on a second monitor to save real estate for code on the main one, using the common pane dock feature available for all dockable panes.
|
|
Sat Jul 15, 2017 11:05 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Good hint, thanks.
|
|
Sat Jul 15, 2017 11:09 am |
|
![](templates/GreenTech/images/spacer.gif) |
Mindflux
Joined: 25 May 2013 Posts: 812 Country: United States |
|
|
|
Very nice. Looks great so far.
:)
|
|
Mon Jul 17, 2017 10:48 am |
|
![](templates/GreenTech/images/spacer.gif) |
|