Check table exists in SQL Server

Published on Thursday, January 20, 2011

How to check a table exists in SQL Server ?

Here we collect 3 methods:

1:

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

2:

SELECT *
FROM sys.tables
WHERE name = 'mytable'
AND schema_id = SCHEMA_ID('myschema')

sys.tables contains all the tables. So it would be easier to query sys.tables rather than sys.objects

3: 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

If you are working with a DB that has no naming conflicts across schemas then simply omitting the "TABLE_SCHEMA = ‘TheSchema’" will work just fine.