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

Following statements check whether the user created table named Employees is exists or not 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 

Following statements check whether the user created temporary or Temp table named #temptablename is exists or not 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%’




 982 total views,  9 views today

Leave a Reply

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