using "IF EXISTS tablebName"

Question

Post reply

using "IF EXISTS tablebName"

winston Smith

SSCoach

Points: 19500
More actions
November 8, 2006 at 2:43 am

#170996

Can you use if exists in this fashon:

if exists table
select * from table
else.....
end if

I have tried a few different ways of doing this but i cant get it to work.

Thanks all

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

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

The correct syntax is look like

if exists

(select * from dbo.sysobjects where id = object_id(N'[table1]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

select * from table1

else

--Print Table1 does'nt exists

end if

Hope this will slove your problem

cheers

Sergiy SSC Guru Points: 110208 More actions · Answer 2

No, it won't work.

You could use dynamic SQL to do this, but I would remind you that shema is an essential part of relational database. And if you at run time don't know if the table exists then you don't have database at all. It's just set of files placed by mistake into some tables.

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Well... it is a bit strange to use test on existence before select, but it is very often used with DDL (IF EXISTS ... DROP TABLE and then CREATE TABLE with the same name). I've seen it so many times, that I consider existence test being "normal". Is it really that bad, Sergiy? I would agree with you if the question would be how to use such test with a variable table name, but this is not the case (at least as I understand the question).

ijaz, END IF is not used in SQL. The block has to be enclosed in BEGIN ... END.

For temporary tables, you can use this:

IF object_id('tempdb..#table1') IS NOT NULL

DROP TABLE #table1

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 4

You could query the system table sysobjects (SQL Server 2K) or sys.objects (SQL Server 2K5):

If Exists(Select * From sysobjects Where type = 'U' and name = 'table')

Select * From table

Else...

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Give it a try.

It works with CREATE/DROP objects, it's OK for temporary objects, but it will fail on compilation time if there is a SELECT from not existing static object.

To compile the code optimiser needs internal addresses for all referenced objects. CREATE/DROP deals with sysobjects, so it will proceed. But attempt to include SELECT from not existing object will not allow to compile SP.

It will not even start execution and will not get to the point IF NOT EXISTS.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 6

One test is worth a thousand words... run the following... do note that the AUTHOR table does NOT exist in PUBS...

USE PUBS

GO

IF OBJECT_ID('dbo.Author') IS NOT NULL --No such table

BEGIN

SELECT * FROM dbo.Author

END

IF OBJECT_ID('dbo.Authors') IS NOT NULL

BEGIN

SELECT * FROM dbo.Authors

END

Anybody see any errors in the run? Me neither... and works fine as a stored proc, as well.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

So what are you trying to accomplish with this? Maybe we could point you in another direction if we had more details!