Determining existance of a Table in SYSOBJECTS
-
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 -
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] -
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 -
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.
-
Thanks for the lead in Frank (my mistake) -- I modified the OBJECTPROPERTY to filter on 'IsUserTable' instead of 'Customers'
BT -
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] -
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] -
Now I understand why you have so many posts Frank... You type 2-3 responses when none or 1 could do
. -
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] -
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')
-
What function?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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?
-
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]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply