'If Exists'

Question

Post reply

'If Exists'

buz_gray

SSC Rookie

Points: 28
More actions
April 26, 2009 at 7:17 am

#130884

Hi!
I want to delete a table if it exists, so I tried this:
USE Test
IF EXISTS (select * from dbo.Table1) DROP table dbo.Table1
Test.dbo.Table1 exists and the result is: Command(s) completed successfully,
BUT the table still exists. Table is dropped if I execute: DROP table dbo.Table1, so the mystery is in the 'IF EXISTS'.
Based on what I've read, this command should work, but it doesn't. Any help?
Thanks!
Buz

Viewing 6 posts - 1 through 6 (of 6 total)

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

You can use SSMS to generate a conditional Drop Table Script.

Right Click on the Table in the Object Explorer window and select “Script Table as …”, “Drop to …”

SSMS will generate something like the following:-

USE [MyDatabse]

GO

/****** Object: Table [dbo].[MyTable] Script Date: 04/26/2009 15:43:51 ******/

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

DROP TABLE [dbo].[MyTable]

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

buz_gray SSC Rookie Points: 28 More actions · Answer 2

Thanks, John.

But I want to build this into a script or procedure. I found a solution that works:

IF object_id('dbo.Table1') is not null DROP table dbo.Table1

I'm new to SQL Server and it's a little exasperating that 'If Exists' should work but seems not to and my alternative does but seems to be little documented!

Cheers,

Buz

John H Marsh SSChampion Points: 11671 More actions · Answer 3

Hello Buz,

I was hoping to show that SSMS is a good friend. You can generate all sorts of useful scripts from it, which you can then cut and paste into your SPs, Maintenance Jobs etc.

It can save you time and headaches.

Cheers,

John

www.sql.lu
SQL Server Luxembourg User Group

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

buz_gray (4/26/2009)
I'm new to SQL Server and it's a little exasperating that 'If Exists' should work but seems not to and my alternative does but seems to be little documented!

Are there any rows in Table1? Exists doesn't check for the existence of an object, it checks for existence of rows. If there are no rows in dbo.Table1, then IF EXISTS (select * from dbo.Table1) will return false.

If you do a exists check like that and the table doesn't exist, the IF EXISTS will throw an error. Error 208 Object not found, as you're trying to query a table that isn't there.

If you want to use EXISTS to check whether a table exists or not, you have to query the catalogue views.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Table1') -- Will return true if Table1 Exists, false if it doesn't

DROP TABLE Table1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

buz_gray SSC Rookie Points: 28 More actions · Answer 5

buz_gray

SSC Rookie

Points: 28

April 26, 2009 at 8:07 am

#984074

A subtlety I missed!

Thanks, John.