Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?
sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?
|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?
|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John
|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>
|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John
|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>
|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb
sql

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:
> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:

> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try i
t
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Serve
r
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to dro
p *system* extended
procs. For this, DENY execute permissions instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try
it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Ser
ver
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation,
and I have an
xp_sendmail in master and none in msdb. If you have something called xp_send
mail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

Drop extended property "MS_Description" of ALL tables and ALL columns

Hi,

Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?

Regards,
Alejandroo

These queries will produce a script that you can run to drop these properties. You can automate with a cursor, or you might want to add a GO to the end of each EXEC statement.

--tables

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id = 0

and extended_properties.name = 'MS_Description'

--columns

select 'EXEC sp_dropextendedproperty

@.name = ''MS_Description''

,@.level0type = ''schema''

,@.level0name = ' + object_schema_name(extended_properties.major_id) + '

,@.level1type = ''table''

,@.level1name = ' + object_name(extended_properties.major_id) + '

,@.level2type = ''column''

,@.level2name = ' + columns.name

from sys.extended_properties

join sys.columns

on columns.object_id = extended_properties.major_id

and columns.column_id = extended_properties.minor_id

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id > 0

and extended_properties.name = 'MS_Description'

|||Thanks a lot Louis, this works perfectly!

drop an extended proc that doesn't exist

the latest hotfix for sql2000 gets an error when it tries
to replace an extended proc named xp_mergexpusage in the
replsys.sql file. Here is a small test case to show the
issue. Any ideas on how to resolve this?
Script:--
select * from sysobjects
where name = 'xp_mergexpusage'
go
execute dbo.sp_dropextendedproc 'xp_mergexpusage'
go
execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
go
result:---
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Procedure
sp_dropextendedproc, Line 18
Cannot drop the procedure 'xp_mergexpusage', because it
does not exist in the system catalog.
Server: Msg 2714, Level 16, State 7, Procedure
sp_addextendedproc, Line 26
There is already an object named 'xp_mergexpusage' in the
database.If you are in this state all sp_addextendedproc calls should fail.
Try running this:
select max(id) from master.dbo.sysobjects
dbcc traceon(3604)
dbcc dbinfo('master')
dbcc traceoff(3604)
What is the value returned by the first query and the value of dbi_nextid =returned by the dbcc dbinfo ?
If they are indentical, try doing:
create table master.dbo.t1(c1 int)
drop table master.dbo.t1
And run the query again, see if the values are no longer identical.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
> the latest hotfix for sql2000 gets an error when it tries
> to replace an extended proc named xp_mergexpusage in the
> replsys.sql file. Here is a small test case to show the
> issue. Any ideas on how to resolve this?
> Script:--
> select * from sysobjects
> where name = 'xp_mergexpusage'
> go
> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
> go
> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> go
> result:---
> (0 row(s) affected)
> Server: Msg 3701, Level 11, State 5, Procedure
> sp_dropextendedproc, Line 18
> Cannot drop the procedure 'xp_mergexpusage', because it
> does not exist in the system catalog.
> Server: Msg 2714, Level 16, State 7, Procedure
> sp_addextendedproc, Line 26
> There is already an object named 'xp_mergexpusage' in the
> database.
>|||You are correct, upon working with it further, later sp_addextendedproc calls did in fact fail.
You solution did rectify the situation. Do you know what series of events gets it into that state?
Thanks,
Gerald
>--Original Message--
>If you are in this state all sp_addextendedproc calls should fail.
>Try running this:
>select max(id) from master.dbo.sysobjects
>dbcc traceon(3604)
>dbcc dbinfo('master')
>dbcc traceoff(3604)
>What is the value returned by the first query and the value of dbi_nextid =3D
>returned by the dbcc dbinfo ?
>If they are indentical, try doing:
>create table master.dbo.t1(c1 int)
>drop table master.dbo.t1
>And run the query again, see if the values are no longer identical.
>GertD@.SQLDev.Net
>Please reply only to the newsgroups.
>This posting is provided "AS IS" with no warranties, and confers no rights.
>You assume all risk for your use.
>Copyright =A9 SQLDev.Net 1991-2003 All rights reserved.
>"Gerald Bowers" <gerald.bowers@.wachovia.com> wrote in message
>news:018201c356cb$5a3294c0$a501280a@.phx.gbl...
>> the latest hotfix for sql2000 gets an error when it tries
>> to replace an extended proc named xp_mergexpusage in the
>> replsys.sql file. Here is a small test case to show the
>> issue. Any ideas on how to resolve this?
>> Script:--
>> select * from sysobjects
>> where name =3D 'xp_mergexpusage'
>> go
>> execute dbo.sp_dropextendedproc 'xp_mergexpusage'
>> go
>> execute sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
>> go
>> result:---
>> (0 row(s) affected)
>> Server: Msg 3701, Level 11, State 5, Procedure
>> sp_dropextendedproc, Line 18
>> Cannot drop the procedure 'xp_mergexpusage', because it
>> does not exist in the system catalog.
>> Server: Msg 2714, Level 16, State 7, Procedure
>> sp_addextendedproc, Line 26
>> There is already an object named 'xp_mergexpusage' in the
>> database.
>
>.
>

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.)