TableExist or not? - 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

General SQL Server Forums

New to SQL Server Programming

TableExist or not?

Author

Topic

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 09:33:27

Dear All,

Is there any way to check whether a table exist in a database by sql query?

SolutionOn

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 09:35:32

Yes.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 09:36:31

SELECT * FROM INFORMATION_SCHEMA.TABLES

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'xyz' AND TABLE_SCHEMA = 'dbo')
PRINT 'Table dbo.xyz exist'
else
PRINT 'Table dbo.xyz does not exist'

E 12°55'05.25"
N 56°04'39.16"

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 09:46:39

Thanks everybody for your great reply

Its working fine

SolutionOn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 10:02:52

Yes. Peso is everybody

You can also use
for SQL 2000

select * from sysobjects where xtype = 'U' and name = 'xyz'

for SQL 2005

select * from sys.tables where type = 'U' and name = 'xyz'

object_id('xyz') is not null

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 10:50:34

Madhi deleted is suggestion with sysobjects.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 04:07:33

quote:
Originally posted by Peso

Madhi deleted is suggestion with sysobjects.

E 12°55'05.25"
N 56°04'39.16"

Yes I was sniped by you and deleted it

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 04:11:09

<<
object_id('xyz') is not null
>>

Better usage

if (objectproperty(object_id('xyz'),'istable'))=1
print 'table exists'
else
print 'table doesnt exist'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 04:23:45

depending on the usage. This will not work

CREATE VIEW xyz AS SELECT today = GETDATE()
go

IF (objectproperty(object_id('xyz'),'istable'))=1
	print 'table exists'
ELSE
BEGIN
	print 'table doesnt exist'
	CREATE TABLE xyz
	(
		col	int
	)
	/*
		Server: Msg 2714, Level 16, State 6, Line 6
		There is already an object named 'xyz' in the database.
	*/
END

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 05:01:03

quote:

Originally posted by khtan

depending on the usage. This will not work

CREATE VIEW xyz AS SELECT today = GETDATE()
go

IF (objectproperty(object_id('xyz'),'istable'))=1
	print 'table exists'
ELSE
BEGIN
	print 'table doesnt exist'
	CREATE TABLE xyz
	(
		col	int
	)
	/*
		Server: Msg 2714, Level 16, State 6, Line 6
		There is already an object named 'xyz' in the database.
	*/
END

KH
[spoiler]Time is always against us[/spoiler]

Yes it is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources