SQL Server 物件存在性檢查完整指令集：表、視圖、預存程序、函數、暫存表

核心原理

SQL Server 透過系統目錄檢視 sys.objects（或舊版 sysobjects）搭配 OBJECT_ID() 函數，可高效判斷物件是否存在。OBJECT_ID('name', 'type') 回傳 NULL 表示不存在。

常用物件類型代碼

統一範本：建立前先刪除

-- 1. 使用者資料表
IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL
    DROP TABLE dbo.MyTable;
GO

-- 2. 暫存表（區域暫存表 #，全域暫存表 ##）
IF OBJECT_ID('tempdb..#MyTemp', 'U') IS NOT NULL
    DROP TABLE #MyTemp;
GO

-- 3. 檢視表
IF OBJECT_ID('dbo.MyView', 'V') IS NOT NULL
    DROP VIEW dbo.MyView;
GO

-- 4. 預存程序
IF OBJECT_ID('dbo.MyProc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.MyProc;
GO

-- 5. 函數（涵蓋 FN/IF/TF）
IF OBJECT_ID('dbo.MyFunc', 'FN') IS NOT NULL
    DROP FUNCTION dbo.MyFunc;
GO

進階檢查：欄位、預設值、約束

-- 欄位是否存在
IF COL_LENGTH('dbo.MyTable', 'MyColumn') IS NOT NULL
BEGIN
    -- 先刪除預設約束（若有）
    DECLARE @df_name sysname;
    SELECT @df_name = name FROM sys.default_constraints
    WHERE parent_object_id = OBJECT_ID('dbo.MyTable')
      AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('dbo.MyTable'), 'MyColumn', 'ColumnId');
    IF @df_name IS NOT NULL
        EXEC('ALTER TABLE dbo.MyTable DROP CONSTRAINT ' + @df_name);

    ALTER TABLE dbo.MyTable DROP COLUMN MyColumn;
END
GO

-- 主鍵/外鍵/唯一約束檢查
IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = OBJECT_ID('dbo.MyTable'))
    ALTER TABLE dbo.MyTable DROP CONSTRAINT PK_MyTable;
GO

查詢所有物件類型代碼

SELECT DISTINCT type, type_desc FROM sys.objects ORDER BY type;

最佳實踐建議

• 統一使用 OBJECT_ID('schema.name', 'type') 兩參數形式，避免同名不同類型物件誤判
• 暫存表必須指定 tempdb.. 前綴
• 部署腳本建議採用「檢查→刪除→建立」三步驟，冪等可重入
• 生產環境變更前務必備份並驗證回滾腳本

總結

掌握 OBJECT_ID() 搭配類型代碼、COL_LENGTH() 檢查欄位、sys.default_constraints 處理預設值，即可編寫健壯、可重入的 SQL Server 物件管理腳本，支援 CI/CD 自動化部署管線。