Check a table exist and Delete

Question

Check a table exist and Delete

Danasegarane.A

SSC Veteran

Points: 265
More actions
November 6, 2007 at 2:57 am

#176736

Hi all,
I want to check weather a table exists.If exist i want to drop it .How can I ?
Thks
Dana

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

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

You can use the bellow syntax; to replace the table name with your desired table name.

if exists (select * from dbo.sysobjects where id = object_id(N'[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [TableName]

GO

cheers

Danasegarane.A SSC Veteran Points: 265 More actions · Answer 2

Danasegarane.A

SSC Veteran

Points: 265

November 6, 2007 at 3:13 am

#748571

Can you please explain the steps.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Dana

What don't you understand? By the way, I take it you're using SQL Server 2005? If so, better to go with this one:

IF EXISTS (

SELECT * FROM sys.objects

WHERE name = 'MyTable' AND type = 'U'

)

DROP TABLE MyTable

Or, for something ANSI-compliant:

IF EXISTS (

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable' AND TABLE_TYPE = 'BASE TABLE'

)

DROP TABLE MyTable

Bear in mind that these queries aren't schema-sensitive(owner-sensitive in SQL 2000 and below). So if tables called MyTable exist in any schema other than the one you're interested in, then this will delete them, which may not be what you want.

John

Danasegarane.A SSC Veteran Points: 265 More actions · Answer 4

Thanks for the Quick Explanation Jhon.

Bear in mind that these queries aren't schema-sensitive(owner-sensitive in SQL 2000 and below). So if tables called MyTable exist in any schema other than the one you're interested in, then this will delete them, which may not be what you want.

This also applies to the previous post alos

Dana

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Dana

That's right. You can make them take account of the schema with a little more work. For example, in my second query, you'd do something like this:

IF EXISTS (

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable' AND TABLE_TYPE = 'BASE TABLE'

AND SCHEMA_NAME = 'dbo'

)

DROP TABLE MyTable

Because it's ANSI-compliant, you can be sure it'll work on any version of SQL Server - past, present or future.

John

ijaz SSCommitted Points: 1912 More actions · Answer 6

I would like to suggest that initially have look on BOL (Book online) provided with SQL SERVER on any artical. It will really save a much time and develop the skill how to get solution to solve a problem.

When you post some question make sure to describe enviroment as well 2000/2005/2008. It will help other to give you correct answer and best solution and save time for all of us.

cheers

Danasegarane.A SSC Veteran Points: 265 More actions · Answer 7

Danasegarane.A

SSC Veteran

Points: 265

November 6, 2007 at 5:43 am

#748603

I am using SQL 2005 🙂

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

IF OBJECT_ID('sometablename,'U') IS NOT NULL

DROP TABLE sometablename

Lookup OBJECT_ID in Books Online... it's worth the read. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)