Get Unused Stored Procedures from Sql Server 2008

While working in projects, I always used to wonder about maintaining Stored Procedures in Sql Server. The way in which we organize them is that we go on adding the required stored procedures with some name convention so that we can identify what is the module to which this SP refers. But as the project goes ahead modifications come, we go on adding required Stored Procedures and some Stored Procedures become obsolete because of business rule changes. But as far as I have observed in IT projects, these stored procedures are kept as it is and when the project completes no one has any idea about which Stored Procedures are currently in use and which are obsolete.

But I have come across this post, which shows how to get probably unused SPs from Sql Server 2008. The query is as follows, which returns SPs which are not in procedure cache.

//The first part gives list of all SPs and also works in SQL2005

Select p.name From sys.procedures as p where p.is_ms_shipped =0

except //The second part works only in SQL 2008

select p.name from sys.procedures as p inner join sys.dm_exec_procedure_stats as q on p.object_id = q.object_id and is_ms_shipped=0

This combine SP gives you probably unused SPs from Database.

Happy Coding !

Popular posts from this blog

Weekend Gateway Trip - Shrivardhan

Image
We were looking for short trip to get refreshed from daily routine. Are you looking for one halt short trip from Pune, then read onwards. We started at 6 PM Saturday from Narhe, Pune and returned back by 7:30 PM on Wednesday. Route that we followed was as below. Pune -> Pirangut -> Mangaon -> Mhasala -> Shrivardhan And return route was Shrivardhan -> Dive Aagar -> Mhasala -> Mangaon -> Pirangut -> Pune In all it was approximately 350 Km drive. You can view this route on Google map here -> Trip Route   About Shrivardhan :- I will say Shrivardhan has got most clean and developed beach in all kokan beaches we visited so far. It has been developed with seating arrangements and provision of lights on the beach. It is great experience seating at beach in the night. You will also get typical rides that you can expect at other beach like Camel ride, Horse ride, Ghoda gadi, beach bike ride. You get couple of water sports options as w
Read more

Cross Apply Incorrect syntax near '.'

Recently I came across an issue in Sql Server 2005, while running CROSS APPLY. The error says that Incorrect syntax near '.'. I tried to figure out all syntax that I could apply but it did not resolve the issue. Actually we need to set sp_dbcmptlevel level to 90. There are good blog posts for this here and here , which can save your valuable time.
Read more

Apply CSS Class based on Browser

  Do you need to modify couple of CSS class attributes depending on browser?   There are various ways to achieve it. But I found this very simple approach for working on this issue.                 There are modifications needed at 2 places in the code. One is in the HTML body / ASPX page and other is in CSS file. Suppose there is DIV in your mark up and you want to apply different CSS class attributes if browse is IE6 / IE7/ IE8. Then just add a container div with id IE7 to this div. It can be added as this on ASPX page (Note- you can add this in Master page / specific page if you are not using Master Pages): - <!--[if IE 7]><div id="ie7"><![endif]-->       <div class=”category”> This is div of interest where ie7 specific rendering should be done.       </div>   <!--[if lte IE 7]></div><![endif]-->                 The second change is in CSS file. All classes which should
Read more