快速找出資料庫中**使用某張表**的所有預存程序:
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 去重,避免同一程序多次引用同表使用 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;
此法對重構、影響分析、文件化極為實用。