EXISTS 的一个通常用法是在运行CREATE语句之前检验表是否存在。有时可能希望删除一个已存在的表,如果表已经存在的话,可能使用ALTER语句或者其他语句修改已存在的表。一种最常用的使用方法如下所示:
IF EXISTS
(SELECT *
FROM sys.objects
WHERE OBJECT_NAME(object_id) = 'foo'
AND SCHEMA_NAME(schema_id) = 'dbo'
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
BEGIN
DROP TABLE dbo.foo
PRINT 'Table foo has been dropped';
END
GO
CREATE TABLE dbo.foo
(
Column1 int IDENTITY(1, 1) NOT NULL,
Column2 varchar(50) NULL
);
创建数据库的小脚本一例:
USE master;
GO
IF NOT Exists
(SELECT 'True'
FROM sys.databases
WHERE name = 'AdventureWorksCreate')
BEGIN
CREATE DATABASE AdventureWorksCreate;
END
ELSE
BEGIN
PRINT 'Database already exists. Skipping CREATE DATABASE Statement';
END
GO