Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 OBJECT_ID('table','U')

Author  Topic 

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-27 : 17:31:22
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

22864 Posts

Posted - 2005-07-28 : 02:17:48
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

22859 Posts

Posted - 2005-07-28 : 02:46:45
I'm curious to know: is OBJECT_ID() now deprecated?

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-28 : 03:01:48
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
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-28 : 03:14:28
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

22859 Posts

Posted - 2005-07-28 : 07:05:40
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

198 Posts

Posted - 2005-07-28 : 10:36:23
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

22859 Posts

Posted - 2005-07-28 : 10:53:13
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

86 Posts

Posted - 2005-08-11 : 05:04:56
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

22864 Posts

Posted - 2005-08-11 : 05:37:29
>>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

86 Posts

Posted - 2005-08-11 : 05:50:17
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

22864 Posts

Posted - 2005-08-11 : 06:05:43
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
   

- Advertisement -