Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: if exists DROP PROCEDURE/VIEW

    how can I drop a propcedure or a view without error for MS SQL 2000/2500

    for a table

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
    DROP TABLE [dbo].[myTABLE]


    and for a procedure

    IF EXISTS ???
    DROP PROCEDURE [dbo].[SP_myTABLE_Count]


    thank you for helping

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Please excuse me, but I find the number of people who can't use google abolutely unbelievable. A search for sys.objects gives the answer, and the type is P for TSQL stored procedures.

    However, keep in mind that SQL Server 2005 hides objects on which you have no access, so even if you query sys.objects first you may get an error, and you should handle that situation.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    please excuse me, but a forum is not google

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by anselme
    please excuse me, but a forum is not google
    I am perfectly aware of that. What I tried to point out is that very simple questions, like this one, can very easily be solved by searching google. And it's even way faster. Even better, by eliminating a few of the simpler questions, the users of the forum can use their time trying to resolve a bit tougher issues, for which there is NOT an obvious solution when searching google. I do think you get my point.

    By all means, do not think of my post as a personal critism, rather a good guideline for the future. My approach is alway as this: Try to solve, search with google, post on forum.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    actually u need not think that hard to get the "if exists" script. sql server can do it for u. right click on any SP from EM and select "generate SQL Script" or from QA Object-Browser select "create drop script" by right clicking on a SP. check the code generated......
    Last edited by upalsen; 05-21-07 at 04:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •