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'
浙公網安備 33010602011771號