Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Also note that if for any reason you need to check for a temporary table you can do this:

if OBJECT_ID('tempdb..#test') is not null
 --- temp table exists

We always use the OBJECT_ID style for as long as I remember

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 

Looking for a table on a different database:

if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'

IF OBJECT_ID('mytablename') IS NOT NULL 

You can use below code

IF (OBJECT_ID('TableName') IS NOT NULL )
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN 
  PRINT 'Table NOT Exists'
END

Or

IF (EXISTS (SELECT * FROM sys.tables WHERE [name] = 'TableName'))
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN 
  PRINT 'Table NOT Exists'
END

Using the Information Schema is the SQL Standard way to do it, so it should be used by all databases that support it. See Approach 1 in this answer.

I know it is an old question but I have found this possibility if you plan to call it often.

create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go

I've had some problems either with selecting from INFORMATIONAL_SCHEME and OBJECT_ID. I don't know if it's an issue of ODBC driver or something.. Queries from SQL management studio, both, were okay.

Here is the solution:

SELECT COUNT(*) FROM <yourTableNameHere>

So, if the query fails, there is, probably, no such table in the database (or you don't have access permissions to it).

The check is done by comparing the value (integer in my case) returned by SQL executor which deals with ODBC driver..

if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) {
  // myTable doesn't exist..
}

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE 
TABLE_CATALOG = 'Database Name' and
TABLE_NAME = 'Table Name' and 
TABLE_SCHEMA = 'Schema Name') -- Database and Schema name in where statement can be deleted

BEGIN
--TABLE EXISTS
END

ELSE BEGIN
--TABLE DOES NOT EXISTS
END

IF EXISTS (   SELECT * FROM   dbo.sysobjects WHERE  id = OBJECT_ID(N'dbo.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
BEGIN
  SELECT * FROM dbo.TableName;
END
GO

i taking here creating a view as example.

Because ALTER/CREATE commands can't be within BEGIN/END blocks. You need to test for existence and the drop it before doing a create

IF Object_ID('TestView') IS NOT NULL
DROP VIEW TestView

GO

CREATE VIEW TestView
   as
   . . .

GO

If you are woried about the permissions being lost you can script the GRANT statements as well and re-run those at the end.

You could wrap the create/alter into a string and do an EXEC - that might get ugly for large views

DECLARE @SQL as varchar(4000)

-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE' 

IF Object_ID('TestView') IS NULL
    SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW TestView AS ' + @SQL

There is one more option to check if the table exists across databases

IF EXISTS(SELECT 1 FROM [change-to-your-database].SYS.TABLES WHERE NAME = 'change-to-your-table-name')
BEGIN
    -- do whatever you want
END

Run this query to check if the table exists in the database:

IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL
PRINT 'Table Exists';