Dropping stored procedures

Hello!
I've written a script to drop all the SPs from a given DB:
DECLARE @.name nvarchar(200)
DECLARE list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'p'
OPEN list
FETCH NEXT FROM list INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.name = 'drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list INTO @.name
END
CLOSE list
DEALLOCATE list
GO
When I'm running it from Query Analyzer it works fine, but when running it
from a small utility (which all id does is opening a connection to the DB
and running the same script), only some of the SPs are dropped.
I'm now looking at the utility, but is there any problem with the SQL scirpt
(or a better way to the task (drop all of the SPs in a DB, without knowing
the names of them all)).
Thanks!
RoeeTry to explicitly spcify the owner of the procedure in the Drop command.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Roee Friedman" <necnecnecnec@.hotmail.com> wrote in message
news:bppqas$1rcn6n$1@.ID-200860.news.uni-berlin.de...
> Hello!
> I've written a script to drop all the SPs from a given DB:
> DECLARE @.name nvarchar(200)
> DECLARE list CURSOR FOR
> SELECT name FROM sysobjects where xtype = 'p'
> OPEN list
> FETCH NEXT FROM list INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.name = 'drop procedure ' + @.name
> EXEC sp_executesql @.name
> FETCH NEXT FROM list INTO @.name
> END
> CLOSE list
> DEALLOCATE list
> GO
> When I'm running it from Query Analyzer it works fine, but when running it
> from a small utility (which all id does is opening a connection to the DB
> and running the same script), only some of the SPs are dropped.
> I'm now looking at the utility, but is there any problem with the SQL
scirpt
> (or a better way to the task (drop all of the SPs in a DB, without knowing
> the names of them all)).
> Thanks!
> Roee
>
>

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)