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
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 -
SQL Cop -
SQL Test -
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!