sqlgraphvizsql-parser
Visualizing what an sql statement is using
I have an SQL statement that uses a few tables built via another set of statements which in turn are each built by more sets of statements etc. to about 8 degrees fanning out to about 25 tables (it gets wider in the 4th degree but that level eventually starts pointing back down to the same few common sources).
I'm working on making a dot file for Graphviz that shows each statement's selected columns, and from which table each comes from. As there's case statements and functions involved, sometimes that's multiple sources per column. Once that goes a few levels it's not only a tad messy, but also hard to read or write correctly.
I've been trying to find if there's a tool that analyzes an SQL select statement and (instead of making an execution plan graph) graphs which ins go to which outs. Preferably the tool would be able to combine this with multiple layers of statements.
Does anyone know of a tool like this?
You see, searching Google for "graph an sql statement" and the like just winds up with lots of results on how to make either charts and graphs with the data from an SQL query, or something specific to graph databases. Neither are here nor there, and changing "graph" to "visualizing" hasn't helped either.
E.G.
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;
Might hopefully lead a tool to draw up something like:
By basically parsing the above SQL and (one option) generating a DOT file for Graphviz like so:
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 it could use fancy record type node labels to draw the tables a bit.
And, as Graphviz can generate SVG, but StackOverflow can't seem to embed it in any way that I can see, run this SVG snippet to see what it looks like (If imgur embedded above isn't working).
<?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->b_fskuid -->
<g id="edge4" class="edge">
<title>a_idfsk->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->information -->
<g id="edge1" class="edge">
<title>a_info->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->a_idfsk -->
<g id="edge3" class="edge">
<title>b_fskuid->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->information -->
<g id="edge2" class="edge">
<title>b_inform->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->a_idfsk -->
<g id="edge6" class="edge">
<title>c_sku_id->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->a_info -->
<g id="edge5" class="edge">
<title>c_short_sku_desc->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->b_fskuid -->
<g id="edge7" class="edge">
<title>d_sku_id->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->b_inform -->
<g id="edge8" class="edge">
<title>d_sku_blurb->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>Solution
You may be able to use the Visual Explain functionality found in MySQL Workbench. I haven't personally used it but it looks like it may provide better insight 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
- PL/SQL arabic displayed as?
- Tuple matching using SQLAlchemy
- How do I copy data from one column to another in the same table?
- Insert leading spaces, then the value to a varchar(10) column
- BigQuery groupby, add row with count of 0 for groupby variable if no rows found
- Calculating difference of dates In Postgresql
- Count / Sum JSON variant attribute Flatten
- MySQL: How to convert seconds to mm:ss format?
- Get mySQL MONTH() to use leading zeros?
- How to use order by with union all in sql?
- Create date periods based on two tables
- Unable to install SQL Server(setup.exe) Exit code decimal: -2061893606
- SQL Server TRIM character
- No PK or FK when exporting SQL Server database
- SQL Server SELECT LAST N Rows
- Oracle SQL query using regexp_substr to get string between 2 strings
- How to select Users who have not received notifications using SQL?
- Fuzzy phrase similarity in SQL Server
- Union two selects removing duplicates base on some columns (not the full row)
- SQL fill gaps with hold
- BigQuery - Transaction is aborted due to concurrent update against table
- SQL one to one relationship vs. single table
- SQL query to fetch persons with more than one postcode address via join table
- php scrape to write to mysql database error
- php mysql connect and create table with no error but no data
- Create login and logout rows from single row
- Alternatives to sliding window frames with RANGE on snowflake
- Snowflake SQL Range between interval
- Cascading Soft Delete
- ERROR: extra data after last expected column on postgresql