Post reply

How to delete all store procedure from my database ?

  • May 20, 2017 at 8:57 pm

    #325918

    I'm using SQL Server 2008 and its Management Studio.
    I'd like to delete all user defined stored procedure & views.

    Is there any script  to execute ?

  • May 21, 2017 at 7:53 am

    #1943478

    spectra - Saturday, May 20, 2017 8:57 PM

    I'm using SQL Server 2008 and its Management Studio.
    I'd like to delete all user defined stored procedure & views.

    Is there any script  to execute ?

    This is very similar to your previous question about tables.  The mechanism to build the statements is the same, as is the need to execute the statements returned by the query.

    This query will generate the statements to drop your stored procedures:

    SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.objects
    WHERE type = 'P'
    ORDER BY name;

    This  will generate the statements to drop your views:

    SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.objects
    WHERE type = 'V'
    ORDER BY name;

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 21, 2017 at 11:14 am

    #1943481

    that is very much helpful.
    thanks

  • May 21, 2017 at 8:27 pm

    #1943495

    spectra - Sunday, May 21, 2017 11:14 AM

    that is very much helpful.
    thanks

    Glad it worked for you.  Thanks for the feedback.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 23, 2017 at 1:59 am

    #1943713

    Also you can open
    1. Object Explorer Details 
    2.Navigate to Stored Procedures Folder under respective database in Object Explorer--> This will show you all procedure in Object Explorer Details
    3. Select all or required procedures from there and then right click Delete.

  • May 23, 2017 at 5:52 am

    #1943748

    Ed Wagner - Sunday, May 21, 2017 7:53 AM

    spectra - Saturday, May 20, 2017 8:57 PM

    I'm using SQL Server 2008 and its Management Studio.
    I'd like to delete all user defined stored procedure & views.

    Is there any script  to execute ?

    This is very similar to your previous question about tables.  The mechanism to build the statements is the same, as is the need to execute the statements returned by the query.

    This query will generate the statements to drop your stored procedures:

    SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.objects
    WHERE type = 'P'
    ORDER BY name;

    This  will generate the statements to drop your views:

    SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.objects
    WHERE type = 'V'
    ORDER BY name;

    You could also use the specific system views.

    SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.procedures
    ORDER BY name;

    SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.views
    ORDER BY name;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 23, 2017 at 7:51 am

    #1943785

    Luis Cazares - Tuesday, May 23, 2017 5:52 AM

    You could also use the specific system views.


    SELECT 'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.procedures
    ORDER BY name;

    SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
    FROM sys.views
    ORDER BY name;

    They'll definitely work.  I generally use sys.objects for almost everything.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply