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%’
Also Read..