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

Mindflux

Joined: 25 May 2013Posts: 609Country: 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

gemisigo

Joined: 11 Mar 2010Posts: 1390

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.

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.

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

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6484

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

Mindflux

Joined: 25 May 2013Posts: 609Country: United States

SysOp wrote:

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

gemisigo

Joined: 11 Mar 2010Posts: 1390

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

Thu Nov 10, 2016 5:16 pm

Mindflux

Joined: 25 May 2013Posts: 609Country: United States

How do you get that to show up?

*edit: got it.

Thu Nov 10, 2016 5:52 pm

Mindflux

Joined: 25 May 2013Posts: 609Country: 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

gemisigo

Joined: 11 Mar 2010Posts: 1390

Mindflux wrote:

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

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6484

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

gemisigo

Joined: 11 Mar 2010Posts: 1390

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

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6484

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.