View Poll Results: Should SQL Server support DROP IF EXISTS table_name?

Voters
9. You may not vote on this poll
  • Yes, it would be very useful!

    2 22.22%
  • Maybe, but I don't really care.

    4 44.44%
  • No, it isn't useful at all.

    3 33.33%
Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Suburban Philadelphia PA
    Posts
    1

    Question Unanswered: SQL Server suggestion

    MySQL has a useful feature:

    DROP TABLE [IF EXISTS] table_name

    This allows you to optionally drop a table only if it already exists

    Why isn't this supported in Microsoft's version of SQL?

    I have seen a few stored-procedures that test for table
    existence but it seems stupid to have to add a special
    stored procedure to every database I set up.

    Just thought I'd ask this as a stupid question :-)

    Use the poll if you think it would be useful / not useful to have this.

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    personally I don't see the need for that option in MSSQL .

    Being a DBA - you can find out very quickly if the table exists...besides you're generally only going to drop a table if you know it exists in the first place.

    Being a programmer - you can quickly create a script to do this anyway.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: SQL Server suggestion

    You can accomplish the same thing (in nearly the same syntax) by checking:

    IF EXISTS {I forget the exact syntax here}
    BEGIN
    DROP TABLE myTable
    END



    Originally posted by loughin
    MySQL has a useful feature:

    DROP TABLE [IF EXISTS] table_name

    This allows you to optionally drop a table only if it already exists

    Why isn't this supported in Microsoft's version of SQL?

    I have seen a few stored-procedures that test for table
    existence but it seems stupid to have to add a special
    stored procedure to every database I set up.

    Just thought I'd ask this as a stupid question :-)

    Use the poll if you think it would be useful / not useful to have this.

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    It sure would be nice to be able to look up the ISO standard for that. If it's in any of the standards, then I'd vote yes. Otherwise, no.

  5. #5
    Join Date
    Jul 2002
    Location
    Glasgow, Scotland
    Posts
    7
    As it only requires one short line of SQL before the DROP statement, I don't see much need for this

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have a better idea

    just drop the table

    if it existed, then it's gone

    if it didn't exist, then it's still gone


    rudy

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I would like to have something similar to Oracle

    CREATE OR REPLACE

    in MSSQL.

    Now I must use ALTER and CREATE separate scripts.


    Code

    IF OBJECT_ID('Your_table','U') is not null DROP TABLE Your_table
    GO
    CREATE TABLE Your_table ...

    seems nice, but if you have an error in CREATE (not only tables, but also triggers,SPs,...), you are in real trouble
    You will see, what Einstein missed in the theory of relativity. Time can also speed up.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The same functionality exists in MSSQL:

    Code:
    if ((object_id('<your table name>') is not null and objectproperty(object_id('<your table name>'),'IsTable') = 1)) drop table <your table name>
    go
    As for placing a special sp in each DB, that isn't necessary, by nameing your stored procedure as 'sp_' + <sp name> and placing the sp in the master db you can execute your special sp from any db just by referencing it's name, i.e. sp_MySP rather than master.dbo.sp_MySP.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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