SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


drop table if exists


drop table if exists

Author
Message
C3PO's twin
C3PO's twin
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 104
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


ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30385 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ian_dunlop2000
ian_dunlop2000
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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]
Ivanna Noh
Ivanna Noh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 3047
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...
dipti.patil
dipti.patil
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 31
This is what I normaly use:

IF OBJECT_ID('tempdb..##tbltmp', 'U') IS NOT NULL
DROP TABLE ##tbltmp
jason-550617
jason-550617
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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]
lorisj33
lorisj33
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 308
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.
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30385 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
lorisj33
lorisj33
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 308
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search