Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

drop table if exists Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2008 2:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 8, 2013 7:11 PM
Points: 17, Visits: 103
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


Post #478289
Posted Wednesday, April 2, 2008 2:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #478292
Posted Wednesday, April 2, 2008 3:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 13, 2008 11:36 AM
Points: 10, Visits: 33
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]
Post #478315
Posted Thursday, April 3, 2008 4:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:29 PM
Points: 830, Visits: 2,464
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...
Post #479626
Posted Friday, April 4, 2008 3:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 30, 2009 8:58 AM
Points: 14, Visits: 31
This is what I normaly use:

IF OBJECT_ID('tempdb..##tbltmp', 'U') IS NOT NULL
DROP TABLE ##tbltmp
Post #479769
Posted Monday, November 24, 2008 5:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 25, 2008 8:48 AM
Points: 1, Visits: 6
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].[table]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[table]
Post #607960
Posted Wednesday, January 27, 2010 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 18, Visits: 251
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.
Post #854354
Posted Wednesday, January 27, 2010 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #854363
Posted Wednesday, January 27, 2010 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 18, Visits: 251
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.
Post #854397
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse