|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to check if #temp table exists?Create Table #temp... and do stuff - without dropping #temp. Then I tried using the following in Query Analyzer if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#temp] and then Create Table #temp... again. But I get the error message that #temp already exists. I am guessing that dbo is tempDB. How do I reference temDB? How can I detect if it exists so I can drop #temp? Thanks, Rich > But I get the error message that #temp already exists. Why are you using temp tables? Why wouldn't you know if the temp table already exists? If the table is of the same structure, why not just empty the table and re-use it? If it is not of the same structure, why not drop it when you are done, so you can re-create it? Or use different names for different temp tables? Anyway, the answer about how to determine if a temp table exists is here: http://www.aspfaq.com/2458 > I am guessing that dbo is tempDB. I have no idea what you mean. dbo is the built in database owner. tempDB is a database. Try,
if object_id('tempdb..#temp') is not null print 'exists.' else print 'does not exist.' AMB Show quote "Rich" wrote: > I create a temp table > > Create Table #temp... > > and do stuff - without dropping #temp. Then I tried using the following in > Query Analyzer > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table [dbo].[#temp] > > and then > > Create Table #temp... again. But I get the error message that #temp > already exists. I am guessing that dbo is tempDB. How do I reference temDB? > How can I detect if it exists so I can drop #temp? > > Thanks, > Rich Alejandro Mesa wrote:
> Try, Very cool. I like how the function is smart enough to look for an owned > > if object_id('tempdb..#temp') is not null > print 'exists.' > else > print 'does not exist.' > temporary table. Here's a question: Where in sysobjects is stored the session that owns the temp table? Is it just a part of the extended temp table object name? David,
No idea. The only thing I know is that sql server use a system-generated numeric suffix that is appended to the table name. I do not know if this number is unique for all temporary tables or if it is unique by users. AMB Show quote "David Gugick" wrote: > Alejandro Mesa wrote: > > Try, > > > > if object_id('tempdb..#temp') is not null > > print 'exists.' > > else > > print 'does not exist.' > > > > Very cool. I like how the function is smart enough to look for an owned > temporary table. > > Here's a question: Where in sysobjects is stored the session that owns > the temp table? Is it just a part of the extended temp table object > name? > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > > Thank you all for your replies. Basically, I wanted to know how you check
for the existence of a temp table. As I expected, you reference tempDB. As for why I would want to check for the existence of a #temp table - safety. If someone needs to rerun an SP where #temp is created (and dropped of course) just making sure it was dropped before recreating it. Show quote "Rich" wrote: > I create a temp table > > Create Table #temp... > > and do stuff - without dropping #temp. Then I tried using the following in > Query Analyzer > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[#temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table [dbo].[#temp] > > and then > > Create Table #temp... again. But I get the error message that #temp > already exists. I am guessing that dbo is tempDB. How do I reference temDB? > How can I detect if it exists so I can drop #temp? > > Thanks, > Rich On Thu, 14 Jul 2005 09:57:04 -0700, Rich wrote:
> Thank you all for your replies. Basically, I wanted to know how you check A different way to guard against that possibility is to use table variables> for the existence of a temp table. As I expected, you reference tempDB. As > for why I would want to check for the existence of a #temp table - safety. > If someone needs to rerun an SP where #temp is created (and dropped of > course) just making sure it was dropped before recreating it. instead of temp tables.  
Other interesting topics
|
|||||||||||||||||||||||
