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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-22 : 23:33:53
|
Peter writes "When I'd like to check whether or not a temporary table exists before creating it, I can use this statement:
IF NOT EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name like '#temptable%')
BEGIN
Drop table #temptable
END
Create table #temptable (a int)
However, if there are more temptables with a name like '#temptable%' in sysobjects, this does not always work.
I also tried this:
select @tempid = id from tempdb..sysobjects WHERE [name] like '#temptable%'
and crdate = (select max(crdate) from tempdb..sysobjects WHERE [name] like '#temptable%' and crdate <= getdate())
IF NOT EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id = @tempid)
BEGIN
Drop table #temptable
END
Create table #temptable (a int)
This still is not 100% waterproof. Do you have any suggestions?
I'm using Windows NT 4.00.1381 and SQL Server 7.0 sp 3." |
|
|
|
|
|
Checking existence of a temporary table