asked
Analysis tool
Hi, 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
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
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.
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!
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!
No tool will do that for you. You have to use the grey matter between your two ears for this one.
21 People are following this question.
