How to Check Whether a Global Temporary Exists in a SQL
Database or not?
Answers were Sorted based on User's Feedback
Answer / guest
Checking whether a table exists in a Microsoft SQL Server
database is easy. You can use this query:
SELECT 'x'
FROM sysobjects
WHERE type = 'U' and NAME = 'mytable'
But this query will not work while searching for global
temporary tables. Global temporary tables are stored in tempdb.
Use this syntax for the search:
DECLARE @temp_table VARCHAR(100)
SET @temp_table = '##my_temp_table'
IF NOT EXISTS (SELECT 'x'
FROM tempdb..sysobjects
WHERE type = 'U' and NAME = @temp_table)
PRINT 'temp table ' + @temp_table + ' does not exist'
ELSE
PRINT 'temp table ' + @temp_table + ' exists.'
Note: You cannot search for local temporary tables (# prefix
tables) in this way. This is because SQL Server appends a
unique number to the name you supply. For example, if you
specified "#temp," the name in sysobjects would be something
like "#temp____1234."
Is This Answer Correct ? | 2 Yes | 0 No |
What is plan freezing?
What are the factors you will check for the performane optimization for a database query?
7 Answers CarrizalSoft Technologies, DELL, SoftSol,
Explain index in sql server?
Does the order of columns in update statements matter?
How to drop an existing stored procedure in ms sql server?
Advantages and Disadvantages of Cursor?
15 Answers Polytechnic, TCS, Zenith,
Differences between functions and stored procedures?
8 Answers 247Customer, Accenture,
Explain the architecture of ms sql reporting service?
What is the difference between implicit and explicit transaction?
What are different types of subquery?
What are the types of lock supported by ?
What is the disadvantages of index?