| Author |
 Topic  |
|
|
TimS
Posting Yak Master
USA
198 Posts |
 Posted - 07/27/2005 : 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
India
22461 Posts |
Posted - 07/28/2005 : 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 |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/28/2005 : 02:46:45
|
I'm curious to know: is OBJECT_ID() now deprecated?
Kristen |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/28/2005 : 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
|
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/28/2005 : 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
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/28/2005 : 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
|
|
|
|
TimS
Posting Yak Master
USA
198 Posts |
Posted - 07/28/2005 : 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. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/28/2005 : 10:53:13
|
When I get to the point that something becomes a pain to type then I start generating it autoMagically instead 
Kristen |
|
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 08/11/2005 : 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
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/11/2005 : 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 |
|
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 08/11/2005 : 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
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 08/11/2005 : 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 |
|
|
| |
Topic |
|
OBJECT_ID('table','U')
