Technical Article

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.  

CREATE PROCEDURE drop_and_recreate_all
@type char(1) 
/* 'p' = stored procedure, 'v' = view' */
as

set nocount on

select 'drop procedure ' + name + char(10) + 'go' + char(10) + 
text + char(10) + 'go' + char(10) + char(10)
from sysobjects o
join syscomments c
on o.id = c.id
where o.type = @type
and OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 
/* skips system objects */

Rate

1.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

1.67 (3)

You rated this post out of 5. Change rating