Check if table exists in SQL Server

select name from SysObjects where xType='U' and name like '%xxx%' order by name

Something important to know for anybody who hasn't found their solution yet:
**SQL server != MYSQL**.
If you want to do it with **MYSQL**, it is quite simple

$sql = "SELECT 1 FROM `db_name`.`table_name` LIMIT 1;";
$result = mysql_query($sql);
if( $result == false )
echo "table DOES NOT EXIST";
else
echo "table exists";

Posting this here because it's the top hit at Google.

If this is to be the 'ultimate' discussion, then it should be noted that Larry Leonard's script can query a remote server as well if the servers are linked.

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

Please see the below approaches,

**Approach 1: Using INFORMATION_SCHEMA.TABLES view**

We can write a query like below to check if a Customers Table exists in the current database.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers')
BEGIN
PRINT 'Table Exists'
END

**Approach 2: Using OBJECT_ID() function**

We can use OBJECT_ID() function like below to check if a Customers Table exists in the current database.

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END


**Approach 3: Using sys.Objects Catalog View**

We can use the Sys.Objects catalog view to check the existence of the Table as shown below:

IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END


**Approach 4: Using sys.Tables Catalog View**

We can use the Sys.Tables catalog view to check the existence of the Table as shown below:

IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'Customers' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END


**Approach 5: Avoid Using sys.sysobjects System table**

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.tables instead of sys.sysobjects system table directly.

IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Customers' AND xtype = N'U')
BEGIN
PRINT 'Table Exists'
END





referred from:

[To see links please register here]

Just adding here, for the benefit of developers and fellow DBAs

a script that receives @Tablename as a parameter

(which may or may not contain the schemaname) and returns the info below if the schema.table exists:



the_name object_id the_schema the_table the_type
[Facts].[FactBackOrder] 758293761 Facts FactBackOrder Table


I produced this script to be used inside other scripts every time I need to test whether or not a table or view exists, and when it does, get its object_id to be used for other purposes.

It raises an error when either you passed an empty string, wrong schema name or wrong table name.

this could be inside a procedure and return -1 for example.

As an example, I have a table called "Facts.FactBackOrder" in one of my Data Warehouse databases.

This is how I achieved this:





PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME()
PRINT ''
GO

SET NOCOUNT ON
GO

--===================================================================================
-- @TableName is the parameter
-- the object we want to deal with (it might be an indexed view or a table)
-- the schema might or might not be specified
-- when not specified it is DBO
--===================================================================================

DECLARE @TableName SYSNAME

SELECT @TableName = 'Facts.FactBackOrder'
--===================================================================================
--===================================================================================
DECLARE @Schema SYSNAME
DECLARE @I INT
DECLARE @Z INT

SELECT @TableName = LTRIM(RTRIM(@TableName))
SELECT @Z = LEN(@TableName)

IF (@Z = 0) BEGIN

RAISERROR('Invalid @Tablename passed.',16,1)

END

SELECT @I = CHARINDEX('.',@TableName )
--SELECT @TableName ,@I

IF @I > 0 BEGIN

--===================================================================================
-- a schema and table name have been passed
-- example Facts.FactBackOrder
-- @Schema = Fact
-- @TableName = FactBackOrder
--===================================================================================

SELECT @Schema = SUBSTRING(@TABLENAME,1,@I-1)
SELECT @TableName = SUBSTRING(@TABLENAME,@I+1,@Z-@I)



END
ELSE BEGIN

--===================================================================================
-- just a table name have been passed
-- so the schema will be dbo
-- example Orders
-- @Schema = dbo
-- @TableName = Orders
--===================================================================================

SELECT @Schema = 'DBO'


END

--===================================================================================
-- Check whether the @SchemaName is valid in the current database
--===================================================================================

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.SCHEMATA K WHERE K.[SCHEMA_NAME] = @Schema ) BEGIN

RAISERROR('Invalid Schema Name.',16,1)

END

--SELECT @Schema as [@Schema]
-- ,@TableName as [@TableName]


DECLARE @R1 TABLE (

THE_NAME SYSNAME
,THE_SCHEMA SYSNAME
,THE_TABLE SYSNAME
,OBJECT_ID INT
,THE_TYPE SYSNAME
,PRIMARY KEY CLUSTERED (THE_SCHEMA,THE_NAME)

)

;WITH RADHE_01 AS (
SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.NAME) AS [the_name]
,the_schema=SCHEMA_NAME(O.schema_id)
,the_table=O.NAME
,object_id =o.object_id
,[the_type]= CASE WHEN O.TYPE = 'U' THEN 'Table' ELSE 'View' END
from sys.objects O
where O.is_ms_shipped = 0
AND O.TYPE IN ('U','V')
)
INSERT INTO @R1 (
THE_NAME
,THE_SCHEMA
,THE_TABLE
,OBJECT_ID
,THE_TYPE
)
SELECT the_name
,the_schema
,the_table
,object_id
,the_type
FROM RADHE_01
WHERE the_schema = @Schema
AND the_table = @TableName

IF (@@ROWCOUNT = 0) BEGIN

RAISERROR('Invalid Table Name.',16,1)

END
ELSE BEGIN

SELECT THE_NAME
,THE_SCHEMA
,THE_TABLE
,OBJECT_ID
,THE_TYPE

FROM @R1

END

In **SQL Server 2000** you can try:

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
SELECT 1 AS 'res'
END

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

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

IF OBJECT_ID('mytablename') IS NOT NULL

Just wanted to mention one situation where it would probably be a little easier to use the `OBJECT_ID` method. The `INFORMATION_SCHEMA` views are objects under each database-

> The information schema views are defined in a special schema named
> INFORMATION_SCHEMA. This schema is contained in each database.

[To see links please register here]

Therefore all tables you access using

IF EXISTS (SELECT 1
FROM [database].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='mytablename')
SELECT 1 AS res ELSE SELECT 0 AS res;

will only reflect what is in `[database]`. If you wanted to check if tables in *another* database exist, without dynamically changing the `[database]` each time, `OBJECT_ID` will let you do this out of the box. Ex-

IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;

works just as well as

IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;



**SQL SERVER 2016 Edit**:

Starting with 2016, Microsoft simplified the ability to check for non-existent objects prior to dropping, by adding the `if exists` keywords to `drop` statements. For example,

drop table if exists mytablename

will do the same thing as `OBJECT_ID` / `INFORMATION_SCHEMA` wrappers, in 1 line of code.

[To see links please register here]

-- -- -----------------------------------------------------
-- -- create procedure to check if a table exists
-- -- -----------------------------------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `checkIfTableExists`;

CREATE PROCEDURE checkIfTableExists(
IN databaseName CHAR(255),
IN tableName CHAR(255),
OUT boolExistsOrNot CHAR(40)
)

BEGIN
SELECT count(*) INTO boolExistsOrNot FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = databaseName)
AND (TABLE_NAME = tableName);
END $$

DELIMITER ;


-- -- -----------------------------------------------------
-- -- how to use : check if table migrations exists
-- -- -----------------------------------------------------
CALL checkIfTableExists('muDbName', 'migrations', @output);