Visualization of what sql statement uses

Question

I have an SQL statement that uses several tables built using another set of statements, which, in turn, are built from many sets of statements, etc. up to about 8 degrees, swinging up to about 25 tables (it expands to the 4th degree but this level eventually begins to point to the same common sources).

I am working on creating a point file for Graphviz that shows each statement selected by columns and from which each of these tables comes from. As with assertions and functions, sometimes with multiple sources per column. Once this happens on several levels, it is not only a little dirty, but also difficult to read or write correctly.

I am trying to find if there is a tool that parses the SQL select statement and (instead of the execution plan graph) graphs that get to which outputs. Preferably, the tool could combine this with several levels of statements.

Does anyone know about such a tool?

You see that Google search for “SQL query graph”, etc. just leads to great results on creating charts and graphs with data from an SQL query or something specific for graphical databases. Neither here nor there, and changing the "schedule" to "visualization" also did not help.

eg.

SELECT CASE WHEN A.info IS NULL THEN 'Jam' WHEN B.inform LIKE 'N/A%' THEN 'Butter' WHEN A.info > 4 THEN 'Apricot' ELSE 'Organic' END AS information, -- More of the same FROM 2_KU_4_SKU AS A LEFT JOIN SKU_HOWS_ROC AS B ON A.fskuid = B.idfsk; INSERT INTO 2_KU_4_SKU -- More case statements and other stuff but lets just say sku_id AS fskuid, short_sku_desc AS info FROM BASIC_SKU_TABLE_NOT_A_VIEW_TEDS_VERSION_LATEST_Q108; INSERT INTO SKU_HOWS_ROC -- You guessed it sku_id AS idfsk, -- Because words sku_blurb AS inform FROM BASIC_SKU_TABLE_STILL_NOT_A_VIEW_BILLS_EXCELLENT_VERSION; 

Perhaps we will find a tool to create something like:

Basically analyzing the above SQL and (one option) generating a DOT file for Graphviz as follows:

 Digraph G { fontname = "sans-serif"; node [fontname="sans-serif",shape="record"]; subgraph cluster0 { label="SELECT"; information; } subgraph cluster1 { label="SKU_HOWS_ROC"; a_idfsk [label="idfsk"]; a_info [label="info"]; } a_info -> information; subgraph cluster2 { label="2_KU_4_SKU"; b_fskuid [label="fskuid"]; b_inform [label="inform"]; } b_inform -> information; b_fskuid -> a_idfsk -> b_fskuid; subgraph cluster3 { label="BASIC_SKU_TABLE_NOT_A_VIEW_TEDS_VERSION_LATEST_Q108"; c_sku_id [label="sku_id"]; c_short_sku_desc [label="short_sku_desc"]; } c_short_sku_desc -> a_info; c_sku_id -> a_idfsk; subgraph cluster4 { label="BASIC_SKU_TABLE_STILL_NOT_A_VIEW_BILLS_EXCELLENT_VERSION"; d_sku_id [label="sku_id"]; d_sku_blurb [label="sku_blurb"]; } d_sku_id -> b_fskuid; d_sku_blurb -> b_inform; } 

It would be even better if he could use bright labels like node to draw tables.

And since Graphviz can generate SVG, but StackOverflow cannot embed it in any way that I can see, run this SVG snippet to see what it looks like (if imgur embedded does not work above).

 <?xml version="1.0" encoding="UTF-8" standalone="no" ?> <!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd"> <!-- Generated by graphviz version 2.39.20160727.0821 (20160727.0821) --> <!-- Title: G Pages: 1 --> <svg viewBox="0.00 0.00 988.00 265.00" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"> <g id="graph0" class="graph" transform="scale(1 1) rotate(0) translate(4 261)"> <title>G</title> <polygon fill="white" stroke="transparent" points="-4,4 -4,-261 984,-261 984,4 -4,4" /> <g id="clust2" class="cluster"> <title>cluster1</title> <polygon fill="none" stroke="black" points="329,-91 329,-166 471,-166 471,-91 329,-91" /> <text text-anchor="middle" x="400" y="-150.8" font-family="sans-serif" font-size="14.00">SKU_HOWS_ROC</text> </g> <g id="clust1" class="cluster"> <title>cluster0</title> <polygon fill="none" stroke="black" points="388,-8 388,-83 490,-83 490,-8 388,-8" /> <text text-anchor="middle" x="439" y="-67.8" font-family="sans-serif" font-size="14.00">SELECT</text> </g> <g id="clust3" class="cluster"> <title>cluster2</title> <polygon fill="none" stroke="black" points="485,-91 485,-166 627,-166 627,-91 485,-91" /> <text text-anchor="middle" x="556" y="-150.8" font-family="sans-serif" font-size="14.00">2_KU_4_SKU</text> </g> <g id="clust5" class="cluster"> <title>cluster4</title> <polygon fill="none" stroke="black" points="477,-174 477,-249 972,-249 972,-174 477,-174" /> <text text-anchor="middle" x="724.5" y="-233.8" font-family="sans-serif" font-size="14.00">BASIC_SKU_TABLE_STILL_NOT_A_VIEW_BILLS_EXCELLENT_VERSION</text> </g> <g id="clust4" class="cluster"> <title>cluster3</title> <polygon fill="none" stroke="black" points="8,-174 8,-249 469,-249 469,-174 8,-174" /> <text text-anchor="middle" x="238.5" y="-233.8" font-family="sans-serif" font-size="14.00">BASIC_SKU_TABLE_NOT_A_VIEW_TEDS_VERSION_LATEST_Q108</text> </g> <!-- information --> <g id="node1" class="node"> <title>information</title> <polygon fill="none" stroke="black" points="396.373,-16.5 396.373,-52.5 481.627,-52.5 481.627,-16.5 396.373,-16.5" /> <text text-anchor="middle" x="439" y="-30.3" font-family="sans-serif" font-size="14.00">information</text> </g> <!-- a_idfsk --> <g id="node2" class="node"> <title>a_idfsk</title> <polygon fill="none" stroke="black" points="409,-99.5 409,-135.5 463,-135.5 463,-99.5 409,-99.5" /> <text text-anchor="middle" x="435.893" y="-113.3" font-family="sans-serif" font-size="14.00">idfsk</text> </g> <!-- b_fskuid --> <g id="node4" class="node"> <title>b_fskuid</title> <polygon fill="none" stroke="black" points="565,-99.5 565,-135.5 619,-135.5 619,-99.5 565,-99.5" /> <text text-anchor="middle" x="591.786" y="-113.3" font-family="sans-serif" font-size="14.00">fskuid</text> </g> <!-- a_idfsk&#45;&gt;b_fskuid --> <g id="edge4" class="edge"> <title>a_idfsk&#45;&gt;b_fskuid</title> <path fill="none" stroke="black" d="M446.616,-135.503C455.979,-149.063 471.07,-165.983 490,-170 504.373,-173.05 544.531,-175.185 556,-166 563.413,-160.064 570.086,-152.258 575.615,-144.648" /> <polygon fill="black" stroke="black" points="578.818,-146.164 581.557,-135.929 573.033,-142.222 578.818,-146.164" /> </g> <!-- a_info --> <g id="node3" class="node"> <title>a_info</title> <polygon fill="none" stroke="black" points="337,-99.5 337,-135.5 391,-135.5 391,-99.5 337,-99.5" /> <text text-anchor="middle" x="363.786" y="-113.3" font-family="sans-serif" font-size="14.00">info</text> </g> <!-- a_info&#45;&gt;information --> <g id="edge1" class="edge"> <title>a_info&#45;&gt;information</title> <path fill="none" stroke="black" d="M380.274,-99.4902C390.62,-88.0401 404.181,-73.0328 415.665,-60.3245" /> <polygon fill="black" stroke="black" points="418.391,-62.5274 422.499,-52.7612 413.198,-57.8343 418.391,-62.5274" /> </g> <!-- b_fskuid&#45;&gt;a_idfsk --> <g id="edge3" class="edge"> <title>b_fskuid&#45;&gt;a_idfsk</title> <path fill="none" stroke="black" d="M581.557,-135.929C575.195,-145.863 566.313,-157.741 556,-166 544.531,-175.185 504.373,-173.05 490,-170 474.767,-166.767 462.021,-155.18 452.744,-143.712" /> <polygon fill="black" stroke="black" points="455.403,-141.423 446.616,-135.503 449.793,-145.61 455.403,-141.423" /> </g> <!-- b_inform --> <g id="node5" class="node"> <title>b_inform</title> <polygon fill="none" stroke="black" points="492.552,-99.5 492.552,-135.5 547.448,-135.5 547.448,-99.5 492.552,-99.5" /> <text text-anchor="middle" x="520" y="-113.3" font-family="sans-serif" font-size="14.00">inform</text> </g> <!-- b_inform&#45;&gt;information --> <g id="edge2" class="edge"> <title>b_inform&#45;&gt;information</title> <path fill="none" stroke="black" d="M502.424,-99.4902C491.145,-87.9321 476.328,-72.7493 463.852,-59.9655" /> <polygon fill="black" stroke="black" points="466.31,-57.4735 456.821,-52.7612 461.301,-62.3626 466.31,-57.4735" /> </g> <!-- c_sku_id --> <g id="node6" class="node"> <title>c_sku_id</title> <polygon fill="none" stroke="black" points="404.766,-182.5 404.766,-218.5 461.234,-218.5 461.234,-182.5 404.766,-182.5" /> <text text-anchor="middle" x="433" y="-196.3" font-family="sans-serif" font-size="14.00">sku_id</text> </g> <!-- c_sku_id&#45;&gt;a_idfsk --> <g id="edge6" class="edge"> <title>c_sku_id&#45;&gt;a_idfsk</title> <path fill="none" stroke="black" d="M433.651,-182.49C434.037,-171.796 434.536,-157.999 434.975,-145.871" /> <polygon fill="black" stroke="black" points="438.476,-145.881 435.34,-135.761 431.481,-145.628 438.476,-145.881" /> </g> <!-- c_short_sku_desc --> <g id="node7" class="node"> <title>c_short_sku_desc</title> <polygon fill="none" stroke="black" points="272.973,-182.5 272.973,-218.5 387.027,-218.5 387.027,-182.5 272.973,-182.5" /> <text text-anchor="middle" x="330" y="-196.3" font-family="sans-serif" font-size="14.00">short_sku_desc</text> </g> <!-- c_short_sku_desc&#45;&gt;a_info --> <g id="edge5" class="edge"> <title>c_short_sku_desc&#45;&gt;a_info</title> <path fill="none" stroke="black" d="M337.377,-182.49C341.847,-171.58 347.639,-157.441 352.679,-145.138" /> <polygon fill="black" stroke="black" points="355.968,-146.342 356.519,-135.761 349.49,-143.688 355.968,-146.342" /> </g> <!-- d_sku_id --> <g id="node8" class="node"> <title>d_sku_id</title> <polygon fill="none" stroke="black" points="579.766,-182.5 579.766,-218.5 636.234,-218.5 636.234,-182.5 579.766,-182.5" /> <text text-anchor="middle" x="608" y="-196.3" font-family="sans-serif" font-size="14.00">sku_id</text> </g> <!-- d_sku_id&#45;&gt;b_fskuid --> <g id="edge7" class="edge"> <title>d_sku_id&#45;&gt;b_fskuid</title> <path fill="none" stroke="black" d="M604.528,-182.49C602.467,-171.796 599.807,-157.999 597.469,-145.871" /> <polygon fill="black" stroke="black" points="600.85,-144.918 595.52,-135.761 593.976,-146.243 600.85,-144.918" /> </g> <!-- d_sku_blurb --> <g id="node9" class="node"> <title>d_sku_blurb</title> <polygon fill="none" stroke="black" points="484.648,-182.5 484.648,-218.5 561.352,-218.5 561.352,-182.5 484.648,-182.5" /> <text text-anchor="middle" x="523" y="-196.3" font-family="sans-serif" font-size="14.00">sku_blurb</text> </g> <!-- d_sku_blurb&#45;&gt;b_inform --> <g id="edge8" class="edge"> <title>d_sku_blurb&#45;&gt;b_inform</title> <path fill="none" stroke="black" d="M522.349,-182.49C521.963,-171.796 521.464,-157.999 521.025,-145.871" /> <polygon fill="black" stroke="black" points="524.519,-145.628 520.66,-135.761 517.524,-145.881 524.519,-145.628" /> </g> </g> </svg> 

Answer 1

You may be able to use the Visual Explain functionality found in MySQL Workbench. I haven’t used it personally, but it looks like it can provide a better understanding than the standard EXPLAIN command. Take a look at this link and good luck!

https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html