How to Check Whether a Global Temporary Exists in a SQL
Database or not?

Answers were Sorted based on User's Feedback



How to Check Whether a Global Temporary Exists in a SQL Database or not?..

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

How to Check Whether a Global Temporary Exists in a SQL Database or not?..

Answer / ish

use tempdb

sp_tables '##%'

Is This Answer Correct ?    3 Yes 1 No

Post New Answer

More SQL Server Interview Questions

Explain the properties of subqueries in sql server?

0 Answers  


What is bit datatype and what's the information that can be stored inside a bit column?

1 Answers  


What are the operating modes in which database mirroring runs?

0 Answers  


What is side by side migration in sql server?

0 Answers  


What is data mart? : sql server analysis services, ssas

0 Answers  






If there is failure during updation of certain rows, what will be the state?

2 Answers  


How data can be copied from one table to another table?

0 Answers  


What are the disadvantages of primary key and foreign key in SQL?

0 Answers   Alcatel-Lucent,


What is measure group, measure? : sql server analysis services, ssas

0 Answers  


What is scan table/view and seek table/view when its occurs? : sql server database administration

0 Answers  


Why do we use non clustered index?

0 Answers  


how to write the query to select the rows are in the order of either 1,3,5,7... or 2,4,6,8,...

7 Answers   ADP, Satyam, Tech Mahindra,


Categories