January 9, 2010 at 2:29 pm
Hi,
I have this query, provided by another user in another forum about a TSQL problem I had:
SELECT 'DROP PROCEDURE ' + name
FROM sysobjects WHERE xtype = 'U' AND
name LIKE 'usp_%' -- Here you'll define your criteria
I know what this does (gets all the SPs I want to delete), however, how do I modify this to actually execute and perform the deletion?
I know this is a very naive question.
Thanks
January 9, 2010 at 3:38 pm
Please note in SQL 2008:
SQL Server 2008 Books Online (November 2009)
sys.objects (Transact-SQL)
Updated: 31 August 2009
Contains a row for each user-defined, schema-scoped object that is created within a database.
Read here:
http://msdn.microsoft.com/en-us/library/ms190324.aspx
In sys.objects a Type of U is a user defined table not a stored procedure. A stored procedure is designated by a Type value of P and this is equally true in sQL 2005
The column Xtype is a reference to the system table sysobjects in SQL 2000.
Can you clarify exactly what you want to do and with what revision of SQL Server.
January 9, 2010 at 4:41 pm
having run the corrected SQL as noted above, you would then copy and paste the result set into another query window and execute it.
having of course determined it is going to drop the procedures you want to drop.
---------------------------------------------------------------------
January 11, 2010 at 2:56 am
select 'drop procedure '+name from sys.procedures where name like'%%'
Copy the result and execute it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply