does the table exist!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

does the table exist!!

Author

Topic

hamzeh
Starting Member

2 Posts

Posted - 2006-01-29 : 09:39:22

hello

can anyone help me plz by telling me what is the approciate statement to check if i have a certain table in the database!!!!?
and if i dont have it,what is the error msg that i receive!!!

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 10:14:20

Here is an example of dropping a table ONLY if it already exists


IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[MyTableName]')
	AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE [dbo].[MyTableName]
END

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 02:26:20

or

IF EXISTS (SELECT * from Information_Schema.tables WHERE table_name='[MyTableName]')
BEGIN
DROP TABLE [dbo].[MyTableName]
END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-30 : 03:13:41

Yeah, you're right, Madhi - I shouldn't be using those SYStem tables any more. But those pesky INFORMATION_SCHEMA views are jolly slow ... (dunno about the TABLES one specifically, but that's been my experience in general)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 03:26:20

>>But those pesky INFORMATION_SCHEMA views are jolly slow

Yes it is.

select table_name from information_Schema.tables where table_name='tbl'
select name from sysobjects where name='tbl'

Execution plan says, first query takes 78.94% cost while the second takes only 21.06%

Madhivanan

Failing to plan is Planning to fail

hamzeh
Starting Member

2 Posts

Posted - 2006-01-30 : 04:06:24

Thanks Madhivanan and Kristen, u really helped me alot:)
thanks guys..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources