Determining existance of a Table in SYSOBJECTS

Question

Post reply

Determining existance of a Table in SYSOBJECTS

Express12

SSCoach

Points: 18514
More actions
April 4, 2005 at 6:49 am

#88549

First half of this Query returns the 'Customers' table from SYSOBJECTS. When I add in the OBJECTPROPERTY predicate, I get nothing. Can anyone see anything wrong w/ the OBJECTPROPERTY predicate constraints here?
This returns Customers:
select * from dbo.sysobjects where id = object_id('Customers')
This returns nothing:
select * from dbo.sysobjects where id = object_id('Customers')
and OBJECTPROPERTY(id, 'Customers') = 1)
BT

Viewing 13 posts - 1 through 12 (of 12 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

and OBJECTPROPERTY(id, 'Customers') = 1)

Which OBJECTPROPERTY are you interested in? "Customers" ?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Express12 SSCoach Points: 18514 More actions · Answer 2

All I need to do is determine the existance of a TABLE. Then I do a:

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

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

begin

drop table [dbo].[Customers]

commit

end

select * into Customers from CustomersHOLD

With my current OBJECTProperty predicate, looks like I'm filtering out a table which is actually defined in the catalog.

BT

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

But you are using OBJECTPROPERTY incorrectly. The first parameter (which is called id) should contain the object_id of the object you are checking a property for, and the second parameter should be a string with the name of the property you want.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Express12 SSCoach Points: 18514 More actions · Answer 4

Thanks for the lead in Frank (my mistake) -- I modified the OBJECTPROPERTY to filter on 'IsUserTable' instead of 'Customers'

BT

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

More than one way to skin that cat. Personally I prefer

IF OBJECT_ID('authors') IS NOT NULL

PRINT 'Hello World'

However, Chris and I were focusing on your incorrect use of OBJECTPROPERTY. This should work

IF OBJECT_ID('authors') IS NOT NULL

AND OBJECTPROPERTY(OBJECT_ID('authors'),'IsUserTable')=1

PRINT 'Strike'

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Oh, you've already found out while I was typing

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

Now I understand why you have so many posts Frank... You type 2-3 responses when none or 1 could do .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Yes, and subtract those that are off-topic and you'll get a negative post count

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 9

If calling a function results in row by row processing wouldn't it be better to resort to the old fashioned

IF EXISTS (SELECT 1 FROM dbo.SysObjects WHERE Type='U' AND Name='Customers')

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

What function?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 11

OBJECTPROPERTY and OBJECT_ID

I could be wrong, it has been known before.

I read somewhere about user defined functions that they have to be evaluated on a row by row basis. I don't know if this applies to system functions or not. It is quite possible that OBJECTPROPERTY gets dealt with by converting it to whatever equivalent set based SQL does the trick.

Frank, I've got to ask. You've had the Shark's tale graphic for years but the film only came out in the past year. How come?

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

OBJECTPROPERTY and OBJECT_ID

USE PUBS

SELECT

OBJECTPROPERTY(OBJECT_ID('authors'),'IsMSShipped')

When you examine the execution plan for this, you'll see nothing but Constant Scans. Those should be damn fast. I believe those values to be in some SQL Server internal memory structures readily available. No row-by-row processing as this is the case with scalar UDF's.

As for my avatar: My elder son and I have seen that film I don't know how many times. We both share a great interest for everything about Carcharodon carcharias (aka The Great White Shark). I thought this picture is actually better than some other of real White Sharks. It is less scary

And to be honest, I'm also too lazy to change it anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]