ansaurus

Question

Delete all procedures except those which contain a string in their name

Answer 1

+1 A:

What about:

SELECT 'DROP PROCEDURE ' + name
FROM    sysobjects WHERE xtype = 'U' AND
        name LIKE 'usp_%'   -- Here you'll define your criteria

After running this, you just need to execute this command output.

Rubens Farias 2010-01-09 14:57:20

Thanks! This brings another important consideration - is it possible to see what a query will return/delete but not actually execute it? I think this was covered in the SQL Server MVP Deep Dives book. Thanks.

dotnetdev 2010-01-09 14:58:30

@dotnetdev - You could accomplish this by executing the query within a transaction, but then rolling it back after you analyze the results.

Randy Minder 2010-01-09 15:03:43

I see. How do I execute the command output? Apologies for the noob like questions.

dotnetdev 2010-01-09 15:17:38

Run this, copy the output into an input window, and then execute THAT. This is a SELECT that generates SQL that you can manually run.

Joe 2010-01-09 17:41:37

ansaurus

tags:

views:

answers:

Delete all procedures except those which contain a string in their name

related questions