Deleting SPs with a certain name

  • 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

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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

    ---------------------------------------------------------------------

  • 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