Check table exists in SQL Server
2011-01-20
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.