Solved

How can you check if a Global Temporary table exists ?

Posted on 2004-08-08
7
484 Views
Last Modified: 2008-01-09
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
Comment
Question by:MargusLehiste
7 Comments
 
LVL 11

Accepted Solution

by:
ram2098 earned 150 total points
Comment Utility
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
 
LVL 11

Expert Comment

by:ram2098
Comment Utility
Also, you cannot have the multiple Global Temporary tables with the same name.
0
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
Or a little shorter:

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

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:MargusLehiste
Comment Utility
kselvia - in 'tempdb..##table_name' what are those 2 dots about?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
Comment Utility
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
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
Comment Utility
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
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now