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

原创于 2016-03-27 18:47:20 发布 · 371 阅读

CC 4.0 BY-SA版权

18 篇文章

订阅专栏

本文介绍了几种使用SQL来检查数据库中指定表是否存在的方法。包括通过INFORMATION_SCHEMA.TABLES、OBJECT_ID方法、sys.Objects和sys.Tables目录视图进行检查的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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