Diagram to Reverse Engineer a SQL Query

Toad for SQL Server has a nice feature to allow you to create a visual diagram of a SQL Server query – ok so whats in it for me you ask ?

Lets say you have been given a dirty big query to troubleshoot – now you need to trawl through the query and pull out the tables, the joins and the filters specified in the where clauses.     Formatting the query in SQL Server Management Studio using colour context can help, but a picture is a thousand reserved words.

Diagramming the query can be very handy to help you understand the query faster and provide a level of documentation which probably doesn’t exist.   So whether you are dealing with lots of complex queries, or reverse engineering the backend of an existing production business intelligence solution, this could be a useful tool for you.

http://www.toadworld.com/Blogs/tabid/67/EntryId/434/Reverse-Engineer-a-SQL-Query.aspx

Note the freeware edition http://www.toadworld.com/Downloads/ToadforSQLServerFreeware/tabid/562/Default.aspx, has the “Send to Query Builder” smoked out so you will need a full license for this functionality.

A license for a full version can be purchased here https://shop.quest.com/682/purl-toad-for-sql-server?x-AdCode=cbshopbtntoadforsqlserver but note it aint cheap.

If you want a cheaper option you could build your own tabular view of INFORMATION_SCHEMA.VIEW_TABLE_USAGE

http://www.mssqltips.com/sqlservertip/1638/using-sql-server-meta-data-to-list-tables-that-make-up-views/ but would require a bit of work to get some recursive logic happening for nested views.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s