T-SQL, Part III: Check table's existance

There are several approaches to achieve so. Just list out all approaches I have tried:

Approach 1: select from INFORMATION_SCHEMA.TABLES

Example:

USE [ACLearn];
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N’Image’)
BEGIN
PRINT ‘Table Exists’
END

 

Approach 2: use the OBJECT_ID() method.

Example:

USE [ACLearn];
GO
IF OBJECT_ID(N’dbo.Image’, N’U’) IS NOT NULL
BEGIN
PRINT ‘Table Exists’
END

 

See more regards OBJECT_ID method in MSDN: https://msdn.microsoft.com/en-us/library/ms190328.aspx

Approach 3: use the sys.Objects, sys.Tables Catalog View

Example:

 

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




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

 

 

是为之记。
Alva Chien
2016.3.27