SQL常用判断检测语句
sql------------------------------------------------------------
-- 判断数据库是否存在
IF EXISTS (
SELECT *
FROM master..sysdatabases
WHERE name = N'DBName'
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建的表名是否存在
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[TableName]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建临时表是否存在
IF Object_Id('Tempdb.dbo.#TempDBName') IS NOT NULL
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建的存储过程名是否存在
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[SPName]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建的视图名是否存在
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[ViewName]')
AND OBJECTPROPERTY(id, N'IsView') = 1
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建的函数名是否存在
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[FunName]')
AND xtype IN (N'FN', N'IF', N'TF')
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 判断要创建的列名是否存在
IF EXISTS (
SELECT o.name AS TableName,
c.name AS ColumnName
FROM sysobjects o INNER
JOIN syscolumns c
ON o.id = c.id
WHERE o.name = 'TableName'
AND c.name = 'ColumnName'
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 或者
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE id IN (SELECT id
FROM syscolumns
WHERE name = 'ColumnName')
AND name = 'TableName'
)
PRINT 'exists'
ELSE
PRINT 'not exists'
------------------------------------------------------------
-- 或者
IF col_length('TableName', 'ColumnName') IS NULL
PRINT 'not exists'
ELSE
PRINT 'exists'
