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.thanksTim S |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-28 : 02:17:48
|
Dont use Object_Id, use thisIf exists(select Table_Name from information_schema.tableswhere table_name='tablename' and table_Type='Base Table')--table existselse--not existsMadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 02:46:45
|
I'm curious to know: is OBJECT_ID() now deprecated?Kristen |
|
|
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 |
|
|
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.thanksTim 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 PUBSGOselect 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
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 |
|
|
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 SIF 1 = OBJECTPROPERTY(object_id(N'[dbo].[MyTableName]'), N'IsUserTable') -- is so much longer, but it is standard. |
|
|
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 |
|
|
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 constraintD = Default or DEFAULT constraintF = FOREIGN KEY constraintL = LogFN = Scalar functionIF = Inlined table-functionP = Stored procedurePK = PRIMARY KEY constraint (type is K)RF = Replication filter stored procedure S = System tableTF = Table functionTR = TriggerU = User tableUQ = UNIQUE constraint (type is K)V = ViewX = Extended stored procedureKapil Arya |
|
|
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 tableIt will return value even if you supply sp name or function nameMadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-11 : 06:05:43
|
WellBut you specified SELECT OBJECT_ID('<database name>..<your table name>')But it should be SELECT OBJECT_ID('<database name>..<object_name>') MadhivananFailing to plan is Planning to fail |
|
|
|