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...
> pretty
>
>
Showing posts with label sprocs. Show all posts
Showing posts with label sprocs. Show all posts
Wednesday, March 21, 2012
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'
> >
> >
>
>
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'
> >
> >
>
>
Subscribe to:
Posts (Atom)