How to Delete All the Stored Procedures in a SQL Server 2005 Database
I've been going through the learning curve with CodeSmith and the initial results are pretty interesting, especially with the free NetTiers template set. Man, does that generate a bunch of stored procs, entity and domain objects and controls! If you get used to the model, you are virtually going to get yourself an entire application in one 5-second CodeSmith "Swoop"!
One of the things I came up with while monkeying around with the Database schema was the need to delete all the generated stored procs so CodeSmith can be pointed back at a "procedure-less" database for another take.
The problem is you don't want to just select everything with type 'p' from SysObjects - because there are system procedures there such as for diagrams. Also, you cannot just do an ad-hoc delete, because you'll get an error message; Sql Server is trying to prevent you from shooting yourself in the foot.
Here is an easy way to do it:
First, right - click on the query pane and choose "Results to text".
Next, execute this script:
Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
What you'll get is a nice list of "Drop Procedure" statements that you can paste into the query editor window and execute.
Voilà! All those nasty stored procs are gone. (Or as one of my redneck friends would say, "Walla! Dose procs done left the buildin' , man!")
One of the things I came up with while monkeying around with the Database schema was the need to delete all the generated stored procs so CodeSmith can be pointed back at a "procedure-less" database for another take.
The problem is you don't want to just select everything with type 'p' from SysObjects - because there are system procedures there such as for diagrams. Also, you cannot just do an ad-hoc delete, because you'll get an error message; Sql Server is trying to prevent you from shooting yourself in the foot.
Here is an easy way to do it:
First, right - click on the query pane and choose "Results to text".
Next, execute this script:
Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
What you'll get is a nice list of "Drop Procedure" statements that you can paste into the query editor window and execute.
Voilà! All those nasty stored procs are gone. (Or as one of my redneck friends would say, "Walla! Dose procs done left the buildin' , man!")
This is an awesome codesnippet! Thanks!
ReplyDeleteCheers for that one, save me some extra time.
ReplyDeleteAwesome!
ReplyDeleteIt is good but it is not perfect because there is a problem if you have a schema (not dbo) with the stored procedure.
ReplyDeleteThe sys.procedures doesn't give the name of the shema but only the schema_id, so I do that :
Select 'Drop Procedure ' + s.Name + '.' + p.Name
from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
Where [type] = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
But there is an other solution : to create a stored proc to do the work :
create Procedure dbo.DeleteAllProcedures
As
declare @schemaName varchar(500)
declare @procName varchar(500)
declare cur cursor
for SELECT s.Name, p.Name
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
ORDER BY s.Name, p.Name
open cur
fetch next from cur into @schemaName, @procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @schemaName + '.' + @procName)
fetch next from cur into @schemaName, @procName
end
close cur
deallocate cur
GO
The 'order by' is not useful, I put here for test.
We can use first :
select 'drop procedure ' + @schemaName + '.' + @procName
before to replace by :
exec('drop procedure ' + @schemaName + '.' + @procName)
to be sure that we drop only the procedures we want to drop.
Cheers,
Dominique Gratpain