T-SQL Check if table exists in schema

Question

So far I've been using the format below for creating/updating tables

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'table_name' AND type = 'U') 
DROP TABLE [dbo].[table_name]
GO
CREATE TABLE [dbo].[table_name]()
GO

But recently I came across a case where two schemas have a table with the same name. How can I check if the table exists in a specific schema? Its only the partSELECT 1 FROM sysobjects WHERE name = 'table_name' AND type = 'U' that needs fixing, I've changed the rest to:

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'table_name' AND type = 'U') 
DROP TABLE [schema_name].[table_name]
GO
CREATE TABLE [schema_name].[table_name]()
GO

My current server version is 2008R2 so I would prefer answers that also work for that version. I have many other checks is done this way so I don't really want to completely change this pattern.

Consider using the more concise OBJECT_ID function: IF OBJECT_ID(N'[schema_name].[table_name]') IS NOT NULL.... — Dan Guzman
– Dan Guzman, Commented Feb 8, 2019 at 11:16

S.Jose · Accepted Answer · 2019-02-08 11:16:32Z

13

TRY

IF OBJECT_ID('[schema_name].[table_name]') IS NOT NULL
        DROP TABLE [schema_name].[table_name]
    GO

answered Feb 8, 2019 at 11:16

S.Jose

2461 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SNR · Accepted Answer · 2019-02-08 11:16:16Z

6

Use this syntax.

DROP TABLE IF EXISTS [schema_name].[table_name]

answered Feb 8, 2019 at 11:16

SNR

7921 gold badge9 silver badges25 bronze badges

1 Comment

Dan Guzman Over a year ago

Note that DROP...IF EXISTS requires SQL Server 2016 or later.

Thom A · Accepted Answer · 2019-02-08 11:17:08Z

5

You could use the schemas object as well. For example:

IF EXISTS (SELECT 1
           FROM sys.tables t
                JOIN sys.schemas s ON t.schema_id = s.schema_id
           WHERE s.[name] = N'schema_name'
             AND t.name = N'table_name'
             AND t.type = 'U')
    DROP TABLE [schema_name].[table_name];
GO

answered Feb 8, 2019 at 11:17

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

3 Comments

Thom A Over a year ago

@nickzoum personally, I suggest the OBJECT_ID answer from S.Jose. It is far more succinct.

nick zoum Over a year ago

Every other check is done this way. I don't really want to completely change the pattern. I also spotted a u_id variable in sysobjects is that not the same as schema_id in sys.tables?

Thom A Over a year ago

Fair enough. Might be worth while in the future if you do want to make it more succinct though :)

Collectives™ on Stack Overflow

T-SQL Check if table exists in schema

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related