Is the following line ok?

Question

Is the following line ok?

Mark Green

SSCrazy

Points: 2085
More actions
May 17, 2007 at 3:05 am

#118760

Is the following T-SQL line ok?

if exists (select 1 from INFORMATION_SCHEMA.tables where table_name = 'Result') and exists (select 1 from INFORMATION_SCHEMA.tables where table_name = 'Time_definition') begin

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

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

I would prefer to use system functions:

IF OBJECTPROPERTY (Object_ID('Result'), 'IsUserTable')= 1 and OBJECTPROPERTY (Object_ID('Time_definition'), 'IsUserTable') = 1

BEGIN

Your query will include views as well.

You need to add "AND TABLE_TYPE = 'BASE TABLE'" to make sure 'Result' is a table.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003891 More actions · Answer 2

I'm right there with Serqiy... I use system functions whenever I can...

I'll probably get my head handed to me for using undocumented features but I believe this works both in 2k and 2k5... a bit of "shorthand" for the Object_ID function...

IF OBJECT_ID('dbo.Result','U') IS NOT NULL

AND OBJECT_ID('dbo.Time_definition,'U') IS NOT NULL

BEGIN

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

Mark Green SSCrazy Points: 2085 More actions · Answer 3

Is it quicker to use OBJECT_ID? If so, any idea how much quicker?

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

Dpends on the queries you build. Many of the system views join many tables, many times even in some cases. So if you can avoid that and do a clustered index search in the system tables instead, you'll definitly get a performance boost.

Jeff Moden SSC Guru Points: 1003891 More actions · Answer 5

Considering that only two rows of a table are being accessed for the original question being asked, I'd have to say the delta-t would be measured in nano-seconds. The big difference is in the readability and the number of characters typed. Also, despite what Microsoft says, the I/S views can change... the functionality of the OBJECT_ID function will not change anymore than, say, the ABS function.

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

Sergiy SSC Guru Points: 110206 More actions · Answer 6

OBJECT_ID('dbo.Result','U') does not work in SQL2000.

Did not try it in 2k yet.

_____________
Code for TallyGenerator

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

It will work without the ", 'U'", since the name of the objects must be unique to a single owner, then there's no point in having to specify the object type has well. Looks like they had to change that in 2k5 tho!

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Actually there is a point to specify object type.

If you check for table existence OBJECT_ID will bring you valid ID and you could end up trying insert something into non-updateable view or even into UDF.

That's why I suggested OBJECTPROPERTY. If object does not exist it will return NULL. If it exists but is not user table it will return 0. And 1 will guarantee you are dealing with table, not anything else.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003891 More actions · Answer 9

Hmmm... I'm using SQL Server 2000 sp4 Developer's Edition and it works fine...

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