Sproc/view/function code parser

Question

Sproc/view/function code parser

jellybean

SSCrazy

Points: 2014
More actions
November 4, 2016 at 10:02 am

#315214

Hi - I'm looking for a code parser for SQL Server 2014 - ie something that I can point at a sproc or view or function and get feedback as regards code quality, readability bulletproofing etc...
Suggestions/comments welcomed,
Regards,
J.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

There is a free tool called 'Data Health Monitor' at databasehealth.com. You basically point it to a SQL Server database, and it performs a variety of analysis at difference levels, one of which is what you've asked for regarding T-SQL objects.

There is a SSMS AddIn called 'Poor Man's T-SQL Formatter' that can auto-format T-SQL code.

http://architectshack.com/PoorMansTSqlFormatter.ashx

As far as code quality from an engineering or performance perspective, this can only be done in any meaningful way by analyzing the execution plan compiled from the T-SQL. SQLSentry has a free tool for this:

https://sentryone.com/plan-explorer

Also, Grant Fritchey has a good book on understanding execution plans.

https://www.red-gate.com/library/sql-server-execution-plans-2nd-edition

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

I think Visual Studio (maybe SSDT?) has some capabilities:

https://msdn.microsoft.com/en-us/library/dd172119(v=vs.100).aspx

SQL Cop definitely does:

http://sqlcop.lessthandot.com/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

jellybean (11/4/2016)
Hi - I'm looking for a code parser for SQL Server 2014 - ie something that I can point at a sproc or view or function and get feedback as regards code quality, readability bulletproofing etc...
Suggestions/comments welcomed,
Regards,
J.

These are very broad requirements and to my knowledge there is no one tool that does it all. Further, there is quite an overlap as i.e. code quality is in a sense compliance to best practices as is readability and bulletproofing/resistance. I normally use static code analysis in combination with performance monitoring solutions to home in on a target set which then I manually inspect, haven't found a more efficient method yet.

😎

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

SQL Code Guard[/url] may be of interest.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

There isn't a perfect way to get this done. I'd suggest looking at SQLCop[/url]. It does a decent, but not great, job at this. I don't know of a tool that does a great job.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Grant Fritchey (11/5/2016)
I don't know of a tool that does a great job.

Heh... brain of a good DBA or Developer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)