drop table if exists

  • Can anyone tell me how I can drop a table if it exists.

    this doesn't work...........

    ALTER PROCEDURE [dbo].[spClearTempCoordtables]

    AS

    BEGIN

    if exists (select * from dbo.coordinates)

    drop table dbo.coordinates

    END

    gives..........

    Msg 208, Level 16, State 1, Procedure spClearTempCoordtables, Line 11

    Invalid object name 'dbo.coordinates'.

    (1 row(s) affected)

    thanks

  • you can use ...

    if object_id('dbo.yourtable') is not null

    begin

    drop table dbo.yourtable

    end

    Keep in mind this is not a best practice because you need elevated auth to be able to drop or create a table.

    - maybe # temp tables may help out (I don't know the procs functional needs)

    - To avoid everyone executing the proc needs to be dbo-member, you could use the with execute as setting.

    (check BOL)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Use below replace GOLD_BROKER_OUTPUT WITH THE TABLE YOU NEED TO DROP IS EXISTS

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

    DROP TABLE [dbo].[GOLD_BROKER_OUTPUT]

  • If you have access to the database via SS Management Studio you can generate the T-SQL needed to drop the table by

    right_clicking on the table and selecting:

    Script Table as | DROP to | New Query Editor Window

    or:

    Script Table as | DROP to | File...

  • This is what I normaly use:

    IF OBJECT_ID('tempdb..##tbltmp', 'U') IS NOT NULL

    DROP TABLE ##tbltmp

  • When you select a "DROP TO" from SQL SSMS, this is the SQL that generates...

    USE [database]

    GO

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].

    ') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

    DROP TABLE [dbo].

  • Thanks for your posting. My scenario was slightly different and your solution didn't work for me as-is. My particular environment is with running SSMS 2008 and pointing to a SQL 2000 DB, so your examples didn't work due to the target DB being SQL Server 2000. Instead, I had to use the following:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<insert_tablename>]')

    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[<insert_tablename>]

    GO

    Which is the SQL 2000 syntax as created by Enterprise Manager script generation.

    Maybe someone else will find this helpful too.

  • Good feedback.

    OT : Keep in mind you should avoid running in lower edition db-level ! So switch to your engines dblevel whenever you can !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Oh yes. I should have mentioned that

    it is being upgraded to 2008 like the rest

    of the servers. DTS rewrites to SSIS need

    to come first, IP, so until then . . .

    : )

    Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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