Check if table exists in SQL Server

Question

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.

Here are two possible ways of doing it. Which one is the standard/best way of doing it?

First way:

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

Second way:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides the simple

SHOW TABLES LIKE '%tablename%'; 

statement. I am looking for something similar.

Answer 1

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

Answer 2

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

Answer 3

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

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

Answer 4

Looking for a table on a different database:

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

Answer 5

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.

https://msdn.microsoft.com/en-us/library/ms186778.aspx

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.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

Answer 6

IF OBJECT_ID('mytablename') IS NOT NULL 

Answer 7

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

Answer 8

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.

Answer 9

IF EXISTS 
(
    SELECT   * 
    FROM     sys.objects 
    WHERE    object_id = OBJECT_ID(N'[dbo].[Mapping_APCToFANavigator]') 
             AND 
             type in (N'U')
)
BEGIN

    -- Do whatever you need to here.

END

Here in the above code, the table name is Mapping_APCToFANavigator.

Answer 10

If you need to work on different databases:

DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'

DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'

DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_CATALOG = @Catalog 
      AND TABLE_SCHEMA = @Schema 
      AND TABLE_NAME = @Table))
BEGIN
   --do stuff
END

Answer 11

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

Answer 12

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

Answer 13

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 

Answer 14

IF EXISTS 
(
    SELECT  * 

    FROM    INFORMATION_SCHEMA.TABLES 

    WHERE   TABLE_SCHEMA = 'PutSchemaHere'     
            AND  
            TABLE_NAME   = 'PutTableNameHere'
)

Answer 15

I think the following query works:

IF EXISTS (select * from sys.tables 
WHERE name='mytablename' )
BEGIN
     print 'table exists in the database'
END

Answer 16

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..
}

Answer 17

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

Answer 18

You can use this :

     IF OBJECT_ID (N'dbo.T', N'U') IS NOT NULL 
        BEGIN 
            print 'deleted table';
            drop table t 
        END
     else 
        begin 
            print 'table not found' 
        end

 Create table t (id int identity(1,1) not null, name varchar(30) not null, lastname varchar(25) null)
 insert into t( name, lastname) values('john','doe');
 insert into t( name, lastname) values('rose',NULL);

 Select * from t
1   john    doe
2   rose    NULL

 -- clean
 drop table t

Answer 19

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

Answer 20

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

Answer 21

why most of user say 'if *** is not null' !?, try 'if *** is null' :)

IF OBJECT_ID(N'[dbo].[Error]', N'U') IS null
BEGIN
    CREATE TABLE [dbo].[Error](
        [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [Values] [nvarchar](MAX) NOT NULL,
        [Created] [datetimeoffset](0) NOT NULL,
        )
END

Answer 22

If anyone is trying to do this same thing in linq to sql (or especially linqpad) turn on option to include system tables and views and do this code:

let oSchema = sys.Schemas.FirstOrDefault(s=>s.Name==a.schema )
where oSchema !=null
let o=oSchema!=null?sys.Objects.FirstOrDefault (o => o.Name==a.item && o.Schema_id==oSchema.Schema_id):null
where o!=null

given that you have an object with the name in a property called item, and the schema in a property called schema where the source variable name is a

Answer 23

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

Answer 24

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.

Answer 25

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'

Answer 26

---

-- -- 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);

Answer 27

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