Deleting all SPROCS and UDFs

I want to be able to delete all the SPROCS and UDF's in my DATABASE - pretty
much bringing it down to just TABLE - data only.
I want to do this in a QUERY - in query analyzer.
I do not want to hardwire the names of each SPROC and UDF as more and more
get added every day.
Any ideas'Take a look at the sysobjects table.
There are several ways to do this.. My quick and dirty is to run a query
similar to this... I don't have a SQL Server handy, so you will have to
edit this appropriately...
SELECT 'DROP PROCEDURE ' + Name FROM sysobjects WHERE TYPE = 'P' ORDER BY
Name
Run this and you should output something like:
DROP PROCEDURE usp_Foo1
DROP PROCEDURE usp_Foo2
DROP PROCEDURE usp_Foo3
Then copy and paste that into the top half of the window and execute it.
You may run into trouble if you have dependencies between these objects...
There are better and more complicated ways to do this, of course this is
just a quick and dirty.
Rick
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1B6F21DC-BB0E-4910-955E-2BF0CF54F8F5@.microsoft.com...
> I want to be able to delete all the SPROCS and UDF's in my DATABASE -
pretty
> much bringing it down to just TABLE - data only.
> I want to do this in a QUERY - in query analyzer.
> I do not want to hardwire the names of each SPROC and UDF as more and more
> get added every day.
> Any ideas'
>|||Thanks for the direction - I used the following:
DECLARE @.PROCNAME VARCHAR(255)
DECLARE @.PARENT VARCHAR(255)
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='P'
and LEFT(NAME,3)<>'dt_' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP PROCEDURE '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='FN'
ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP FUNCTION '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='V'
AND LEFT(NAME,3)<> 'sys' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME
EXEC ('DROP VIEW '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='F' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME+' '+@.PARENT
EXEC ('ALTER TABLE '+@.PARENT+' DROP CONSTRAINT '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1
left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='K' ORDER
BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
WHILE @.@.FETCH_STATUS=0
BEGIN
PRINT @.PROCNAME+' '+@.PARENT
EXEC ('ALTER TABLE '+@.PARENT+' DROP CONSTRAINT '+@.PROCNAME)
FETCH NEXT FROM DROP_CURSOR INTO @.PROCNAME,@.PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
"Rick Sawtell" wrote:
> Take a look at the sysobjects table.
> There are several ways to do this.. My quick and dirty is to run a query
> similar to this... I don't have a SQL Server handy, so you will have to
> edit this appropriately...
> SELECT 'DROP PROCEDURE ' + Name FROM sysobjects WHERE TYPE = 'P' ORDER BY
> Name
> Run this and you should output something like:
> DROP PROCEDURE usp_Foo1
> DROP PROCEDURE usp_Foo2
> DROP PROCEDURE usp_Foo3
> Then copy and paste that into the top half of the window and execute it.
>
> You may run into trouble if you have dependencies between these objects...
> There are better and more complicated ways to do this, of course this is
> just a quick and dirty.
>
> Rick
>
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:1B6F21DC-BB0E-4910-955E-2BF0CF54F8F5@.microsoft.com...
> > I want to be able to delete all the SPROCS and UDF's in my DATABASE -
> pretty
> > much bringing it down to just TABLE - data only.
> >
> > I want to do this in a QUERY - in query analyzer.
> >
> > I do not want to hardwire the names of each SPROC and UDF as more and more
> > get added every day.
> >
> > Any ideas'
> >
> >
>
>

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
Microsoft SQL-DMO
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.
OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.
|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
Microsoft SQL-DMO
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [
] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delet
e
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissio
ns
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:

> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissio
ns
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> it.
> Please help. Thanks.

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
--
Microsoft SQL-DMO
--
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.