Need a RELIABLE table-exist test
-
Does anyone know of a reliable way to determine whether or not a table exists?
I have T-SQL code similar to the following:
if not exists (select * from [##table]) create table [##table] . . .
(etc.)
Of course, this only works if [##table] actually exists which, ironically, is the point of my code. (I don't care if there's data in [##table], I want to know if there is a [##table].)
I've checked resources galore, and have found no satisfactory solution to this. Anyone have any ideas?
Frustrated . . .
-
if exists (select * from
INFORMATION_SCHEMA.TABLES
where
TABLE_NAME = 'Your Table'
and
TABLE_TYPE = 'BASE TABLE' )
begin
--- create table ...
end
* Noel -
or
IF Object_id('##table') > 0
begin
...
end
[Edit]
I don't know what you're talking about Noeld.. this works perfectly on my pc
.BTW.. It's tag team not team tag team.
-
IF Object_id('##table') > 0
begin
...
end
... Tag Team strikes again
[edit:]
Remi, What do you mean by ...team tag team .. ?
* Noel -
First, thanks for the assistance!
So far, it seems to be behaving better. Of course, with the "##" tag, it's a temporary table, but was able to figure out the proper syntax (thank you, Books Online!).
Would this work?
IF Object_id('tempdb..##table') is null create ##table . . . (etc.)
I guess what I'm really asking is when should I check for
object_id() is null
as opposed to
object_id() = 0
?
-
Or better, should I do this?
if object_id() is null or object_id() = 0
?
-
Using Enterprise Manager (or whatever), script a table to file. This gives something like:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[your_table_name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--Your code goes here
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[your_table_name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--Your code goes here
END
GO
You can adjust this for use with Temporary Tables as well. I've found it very effective for such a task.
-
I would only say that you should avoid using system tables in your code as these are not supported and in SQL 2005 this code will fail. Kind of funny though that MS generates this code itself though - maybe we'll see them change this functionality...
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
I just retested my idea and found a few gotchas :
use NorthWind
GO
Select object_id('##temp')
--null
GO
create table ##temp
(
a varchar(10) not null
)
GO
Select* from ##temp
--empty set
Select object_id('##temp')
--null
USE tempdb
Select object_id('##temp')
--1222255034
GO
drop table ##temp
go
It seems you must be in tempdb to check for the global temp table variable or even a local temp table.
Noeld : My mistake I read your post too fast.
-
I guess what I'm really asking is when should I check for object_id() is null object_id will return NULL if the object does not exist or the id of the object if it does exist, so you should check using IS NULL for non existance
It seems you must be in tempdb to check for the global temp table variable or even a local temp table Nope but you must qualify it e.g.
object_id('tempdb.dbo.##temp')
object_id('tempdb.dbo.#temp')
Far away is close at hand in the images of elsewhere.
Anon. -
Thanx David... Always something else to learn
. -
I concur – I did see something in BooksOnline that said you had to use the full qualifier for tempdb. Once I put in the qualifier, it worked like a charm.
Personally, I try to avoid using Enterprise Manager at all costs for scripting; I used Query Manager for this purpose.
Here's what I'm trying to do: this code in question is part of a stored procedure I wrote to update a database. I created this temporary table to keep track of any errors that come up during execution. I do this by setting a variable @@ErrCode=@@Error and checking to see if an error was generated. If it was, I run an INSERT into this temporary table. The table keeps track of the table and column names so I can trace the source of the error. I don't need to store this data long term, only for troubleshooting purposes; hence the reason for using a temporary table.
-
Since object names are unique within a database I use a function like this to test for the existance of things:
Create Function ObjectExists
(
@ObjectName VarChar(256)
)
Returns Bit
As Begin
Declare @b-2 Bit
Set @ObjectName=LTrim(@ObjectName)
If CharIndex('.',@ObjectName)=0 Begin
Set @ObjectName=Case Left(@ObjectName,1)
when '#' then 'TempDB..['+@ObjectName+']'
else '['+@ObjectName+']'
End
End
Set @b-2=Case
when Object_Id(@ObjectName) is Null then 0
else 1
End
Return @b-2
End
Usage:
If Common.dbo.ObjectExists('#tmp')=1 Drop Table #Tmp
If Common.dbo.ObjectExists('Test..Test')=1 Drop Table 'Test..Test'
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Looks good, but for the purposes of what I'm doing, this is overkill. (I'm a big believer that simpler is better.) I went ahead with the simple check I posted earlier, that is:
if object_id(...)=0 or object_id(...) is null
I have it in my SP, and it seems to be working just fine.
In any case, I've gotten a lot of good feedback. Thanks for the posts!
-
Ya, I did that a lot too, but over the years it became generically easier to use the function as it covers all cases of object qualification.
Good luck!
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply