前言

在進行資料庫開發與維護時，經常需要知道資料表、檢視表或預存程序最後的修改時間。這些資訊在追蹤系統變更、問題排除、版本控制時都用得上。本文介紹幾個實用的 SQL 查詢，讓你快速掌握資料庫物件的時間資訊。

查詢資料表與檢視表的修改時間

以下是查詢資料表與檢視表修改時間的SQL：

1
2
3
4
5
6
7
8
SELECT [name]           -- 物件名稱
      ,create_date     -- 建立時間
      ,modify_date     -- 修改時間
      ,type_desc       -- 物件類型
FROM sys.all_objects 
WHERE type_desc = 'USER_TABLE'    -- 使用者資料表
   OR type_desc = 'VIEW'          -- 檢視表
ORDER BY modify_date DESC         -- 依修改時間排序

查詢說明

  • sys.all_objects: SQL Server 的目錄檢視，涵蓋使用者物件（sys.objects）與系統物件（sys.system_objects）的聯集；若只想查使用者自建物件，改用 sys.objects 更精準，且不必擔心帶出系統物件
  • type_desc: 物件類型描述
    • 'USER_TABLE': 使用者建立的資料表
    • 'VIEW': 檢視表
  • ORDER BY modify_date DESC: 依修改時間降冪排序，最新的排在最前面

查詢預存程序的修改時間

如果要查詢預存程序的修改時間，可以使用以下查詢：

1
2
3
4
5
6
SELECT [name]           -- 預存程序名稱
      ,create_date     -- 建立時間
      ,modify_date     -- 修改時間
FROM sys.all_objects 
WHERE type_desc = 'SQL_STORED_PROCEDURE'    -- 限定查詢預存程序
  AND SUBSTRING([name], 1, 3) NOT IN ('sp_', 'dt_', 'xp_')  -- 排除系統預存程序

查詢說明

  • type_desc = 'SQL_STORED_PROCEDURE': 只查詢以 T-SQL 撰寫的預存程序
  • SUBSTRING([name], 1, 3) NOT IN ('sp_', 'dt_', 'xp_'):
    • 過濾掉慣例上屬於系統或工具的命名前綴
    • sp_ 是 SQL Server 系統預存程序的慣用前綴；dt_ 是早期資料庫圖表工具的歷史殘留
    • 注意：xp_（擴充預存程序）在目錄檢視中的 type_desc 是 EXTENDED_STORED_PROCEDURE，不是 SQL_STORED_PROCEDURE，因此已被前一個條件排除，這裡的 xp_ 過濾實際上不會命中任何資料列，但加上無妨
    • 過濾後只顯示使用者建立的自訂預存程序

實際應用範例

這些查詢在以下情況特別有用：

  1. 系統維護

    • 追蹤最近被修改的物件
    • 確認系統更新是否成功部署

  2. 問題排查

    • 當系統發生異常時
    • 可以快速檢查是否有物件被意外修改

  3. 部署驗證

    • 確認最近部署的預存程序是否如預期更新
    • 找出非預期被改動的物件（modify_date 只反映 DDL 異動，不追蹤統計資料更新或索引重建時間）

  4. 版本控制

    • 確認程式碼部署狀態
    • 驗證物件是否為最新版本

注意事項

  1. 執行權限

    • 查詢目錄檢視（sys.all_objects / sys.objects）只需對對應物件擁有任一權限，或具備 VIEW DEFINITION 權限即可看到該物件的後設資料列
    • 不必使用 db_owner 等級帳號；以最小必要權限操作符合安全最佳實踐

  2. 效能考量

    • 這些查詢通常執行很快
    • 但在大型資料庫中，建議加上適當的WHERE條件限制範圍

  3. 時間準確性

    • modify_date 會在物件定義（DDL）變更時更新
    • 純資料異動（INSERT/UPDATE/DELETE）不會影響此時間
    • 此欄位儲存 UTC 時間；若你的 SQL Server 伺服器設定為本地時區（台灣 UTC+8），查出來的時間會比本地時間早 8 小時，比對時需注意換算

結語

sys.all_objects 加上 modify_date 排序，是快速定位「誰最近被動過」的直接手段。注意它只反映 DDL 異動，統計資料更新要查 STATS_DATE()，索引重建時間則沒有直接系統欄位可查。