萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 查詢引用指定表的所有預存程序名稱

快速找出資料庫中**使用某張表**的所有預存程序:

SELECT DISTINCT OBJECT_NAME(id) AS ProcedureName
FROM syscomments
WHERE [text] LIKE '%table_name%'          -- 替換為目標表名
  AND OBJECTPROPERTY(id, 'IsProcedure') = 1
ORDER BY ProcedureName;

說明

  • syscomments 儲存程式碼片段(SQL 2000/2005 相容)
  • OBJECTPROPERTY(id, 'IsProcedure') 過濾僅為預存程序
  • DISTINCT 去重,避免同一程序多次引用同表

進階版(SQL 2005+ 推薦)

使用 sys.sql_modules 取代 syscomments,支援 >4000 字元程式碼:

SELECT DISTINCT OBJECT_NAME(object_id) AS ProcedureName
FROM sys.sql_modules
WHERE definition LIKE '%table_name%'
  AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
ORDER BY ProcedureName;

擴展:同時搜尋函數、觸發器、視圖

SELECT 
    OBJECT_NAME(object_id) AS ObjectName,
    CASE 
        WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1 THEN 'Stored Procedure'
        WHEN OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 THEN 'Table Function'
        WHEN OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
        WHEN OBJECTPROPERTY(object_id, 'IsTrigger') = 1 THEN 'Trigger'
        WHEN OBJECTPROPERTY(object_id, 'IsView') = 1 THEN 'View'
        ELSE 'Other'
    END AS ObjectType
FROM sys.sql_modules
WHERE definition LIKE '%table_name%'
ORDER BY ObjectType, ObjectName;

此法對重構、影響分析、文件化極為實用。

copyright © 萬盛學電腦網 all rights reserved