Analysis tool


I've a Database which contains about 1000s of stored procedures. Here what i need to do is, i need to analyse all the stored procedures by using some tool to get the report like

1.What are all the sps are best fit for production 2.What are all the sps are not best fit for production 3.what are all the tables requires index 4.etc

To get the above report, do v have any tool to analyse????

Regards, Karthik

more ▼

asked Jan 07, 2016 at 04:59 AM in Default

avatar image

11 1 7

Maybe I am wrong, but this seems like a silly question to me.

Without knowing the situation you would use the stored procedure, or the outcome wanted - how would an analysis tool know every be able to work out what stored procedures are not best fit for production?

The easiest solution is to look at the individually and probably take a "if it ain't broke don't fix it" attitude

Jan 07, 2016 at 09:46 AM Gazz

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.

Jan 10, 2016 at 05:39 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It sounds like you are looking for a code sniffer, to a degree. I agree with @Gazz I'm not sure this is EXACTLY what you are looking for but a code sniffer would at least give you an idea of what procedures have bad patterns. In my opinion, most bad patterns are not good for production so I try to eliminate them when I can. @Gazz is correct in that you would need to determine what bad patterns you will allow and which ones you won't. There is usually a balance that needs to be struck depending on your environment and development model.

Depending on your development model, you could also use tools like tsqlt to write unit tests around your SQL code to ensure code quality. If the procedure fails the test, well then it's not fit for production. Just a thought.

There are a couple of products out there:

SQL Enlight - http://www.ubitsoft.com/products/sqlenlight/
SQL Cop - http://sqlcop.lessthandot.com/
SQL Test - http://www.red-gate.com/products/sql-development/sql-test/
tsqlt - http://tsqlt.org/

As far as indexes go, SQL Enlight should tell you if you're missing indexes however I've never used that product for that purpose.

Hope that helps!

more ▼

answered Jan 07, 2016 at 12:23 PM

avatar image

14.4k 3 7 15

(comments are locked)
10|1200 characters needed characters left

No tool will do that for you. You have to use the grey matter between your two ears for this one.

more ▼

answered Jan 07, 2016 at 09:08 PM

avatar image

Alvin Ramard
1.4k 2 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 07, 2016 at 04:59 AM

Seen: 70 times

Last Updated: Jan 10, 2016 at 05:39 PM

Copyright 2018 Redgate Software. Privacy Policy