Home » How to check if User table or Temp table exists in database?

How to check if User table or Temp table exists in database?

How to check if User table or Temp table exists in database?

There are many ways to check whether any user table or temp table exists in database or not.


Following are the T-SQL queries using that you can check whether any user table or temp table exists or not in database.

To Check User table exists in database

1>
SELECT * FROM SYSOBJECTS  WHERE type = ‘U’
AND name = ‘Employees’

2>

IF EXISTS ( SELECT * FROM SYSOBJECTS WHERE type = ‘U’
AND name = ‘Employees’)
BEGIN
—-print ‘table exists’
—-SQL Code

END

3>

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘Employees’)
BEGIN
—-print ‘table exists’
—-SQL Code
END

4>

IF OBJECT_ID(N’Employees’) IS NOT NULL
BEGIN
—-print ‘table exists’
—-SQL Code
END

To Check Temp table exists in database 

1>

IF OBJECT_ID(N’tempdb..#temptablename’) IS NOT NULL
BEGIN
—-print ‘temp table exists’
—-SQL Code
END

2>

SELECT * FROM  tempdb.dbo.sysobjects
WHERE type =‘U’ and id = object_id(N’tempdb..#temptablename’)

3>

SELECT * FROM tempdb.sys.tables WHERE name LIKE ‘#temptablename%’

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.