• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

How can you check if a Global Temporary table exists ?

How can you check if a Global Temporary table exists and if it doesnt - create this table.

You can't have multiple global temporary table with the same name - right?
(although multiple users can create local temporary tables with the same name...)
0
MargusLehiste
Asked:
MargusLehiste
2 Solutions
 
ram2098Commented:
IF NOT EXISTS (SELECT * FROM TEMPDB.dbo.SYSOBJECTS WHERE NAME ='##TEMP_Global')
      CREATE  TABLE  ##TEMP_Global( BATCHNO VARCHAR(3), NOOFRECS int)


This statement creates the Global temp table only if not exists.
0
 
ram2098Commented:
Also, you cannot have the multiple Global Temporary tables with the same name.
0
 
Ken SelviaRetiredCommented:
Or a little shorter:

If Object_ID('tempdb..##table_name') Is Null
  Create Table ##table_name (col1 int)

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
MargusLehisteAuthor Commented:
kselvia - in 'tempdb..##table_name' what are those 2 dots about?
0
 
SjoerdVerweijCommented:
Or, a little more portable:

If Not Exists(Select * From TempDB.Information_Schema.Tables Where Table_Name = '##Table_Name')
  Create Table ##table_name(Col1.Int)

This is guaranteed to work in future versions as well.
0
 
Ken SelviaRetiredCommented:
The full name of a table is dababase.owner.table.  If you do not specify a database or owner, the curret db and your username will be used. tempdb..##table means tempdb database, and your username.  Equivalent to tempdb.dbo.##table if you are logged in as dbo or equivalent.

Sjoerd, Object_ID() is not in danger of being dropped or replaced that I am aware of. Why do you suspect information_schema tables are more portable?
0
 
Ken SelviaRetiredCommented:
I think Sjoerd was refering to Ram2098's answer rathe than mine. Yes, if you need to reference sysobjects, it is better to reference information_schema tables.  Sysobjects may not be supported in the future.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now