Drop and re-create all stored procedures or views

There are times when you may need to drop and re-create all stored procedures and/or views in your database. 

For example, in cases where procedures or views are causing blocked locks or other performance problems, a recent article (http://www.sswug.org/see.asp?s=1166&id=13448) suggested dropping/re-creating procedures and views after a service pack has been installed. 

The installation of a service pack (or other significant configuration alteration) often results in performance problems (acording to this article), which can be resolved in some cases by dropping/re-creating objects.

However, manually dropping and re-creating all objects can be tedious, so I decided to create a more automated way of doing it.  

The following stored procedure, drop_and_recreate_all, takes a single parameter representing the object type, and generates a script for dropping all objects of that type (make sure that "Results in Text" is selected).  You can then paste the results into a new Query Analyzer window and execute it.