SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 OBJECT_ID('table','U')
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TimS
Posting Yak Master

USA
198 Posts

Posted - 07/27/2005 :  17:31:22  Show Profile  Reply with Quote
I have been using OBJECT_ID('tablename','U') IS NULL to verify a table does NOT exist. And, I was wondering is the second parameter of 'U' to mean user table a documented feature? I can't seem to find it in BOL.

thanks

Tim S

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/28/2005 :  02:17:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Dont use Object_Id, use this

If exists(select Table_Name from information_schema.tables
where table_name='tablename' and table_Type='Base Table')
--table exists
else
--not exists

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/28/2005 :  02:46:45  Show Profile  Reply with Quote
I'm curious to know: is OBJECT_ID() now deprecated?

Kristen
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/28/2005 :  03:01:48  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

I'm curious to know: is OBJECT_ID() now deprecated?



It will still work in SQL 2005. This might not be the best approach to take though, since the system tables are completely re-done.

Also, Mad's approach doesn't work if you want to use the system function in your WHERE clause.


-ec
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/28/2005 :  03:14:28  Show Profile  Reply with Quote
quote:
Originally posted by TimS

I have been using OBJECT_ID('tablename','U') IS NULL to verify a table does NOT exist. And, I was wondering is the second parameter of 'U' to mean user table a documented feature? I can't seem to find it in BOL.

thanks

Tim S




I have not seen the 2nd parameter used with OBJECT_ID. I'm surprised it actually works. I did a little test with pubs, interesting.


USE PUBS
GO

select object_id ('pub_info', 'U')
select object_id ('pub_info', 'S')
select object_id ('sysusers', 'S')
select object_id ('sysusers', 'U')
select object_id ('titleview', 'V')
select object_id ('titleview', 'S')

----------- 
357576312

(1 row(s) affected)

            
----------- 
NULL

(1 row(s) affected)

            
----------- 
10

(1 row(s) affected)

            
----------- 
NULL

(1 row(s) affected)

            
----------- 
565577053

(1 row(s) affected)

            
----------- 
NULL

(1 row(s) affected)




-ec

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/28/2005 :  07:05:40  Show Profile  Reply with Quote
I think the way that Generate SQL [out of Enterprise Manager] does it is:

IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[MyTableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyTableName]

Kristen
Go to Top of Page

TimS
Posting Yak Master

USA
198 Posts

Posted - 07/28/2005 :  10:36:23  Show Profile  Reply with Quote
Thanks all for the replies; Guess I will stop using the second parameter.

Tim S

IF 1 = OBJECTPROPERTY(object_id(N'[dbo].[MyTableName]'), N'IsUserTable') -- is so much longer, but it is standard.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/28/2005 :  10:53:13  Show Profile  Reply with Quote
When I get to the point that something becomes a pain to type then I start generating it autoMagically instead

Kristen
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/11/2005 :  05:04:56  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
select ID from sysobjects where xtype = 'u' and name = '<your table name>'

SELECT OBJECT_ID('<database name>..<your table name>')

Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure


Kapil Arya
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/11/2005 :  05:37:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>SELECT OBJECT_ID('<database name>..<your table name>')

Thats not effecient way of checking the existance of the table
It will return value even if you supply sp name or function name

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/11/2005 :  05:50:17  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
Madhivanaa,


i was expalining that object_id function actually gives the ID of the each object and it is returning value from sysobjects table.


Kapil Arya
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/11/2005 :  06:05:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well
But you specified
SELECT OBJECT_ID('<database name>..<your table name>')

But it should be
SELECT OBJECT_ID('<database name>..<object_name>')


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000