DECLARE @procname NVARCHAR(100) = 'dbo.test_tbl'
SELECT DISTINCT @procname procname,SCHEMA_NAME(t.[schema_id]) + '.' + OBJECT_NAME(referenced_major_id)
AS tblname,referenced_major_id
FROM sys.sql_dependencies
AS sd
INNER JOIN sys.tables AS t ON t.[object_id] = sd.referenced_major_id AND
t.[type] = N'U'
WHERE sd.[object_id] = OBJECT_ID(@procname)
DECLARE @procname NVARCHAR(100) = 'dbo.test_tbl'
SELECT DISTINCT o.name procname,SCHEMA_NAME(t.[schema_id]) + '.' + OBJECT_NAME(referenced_major_id)
AS tblname,referenced_major_id
FROM sys.sql_dependencies
AS sd
INNER JOIN sys.tables AS t ON t.[object_id] = sd.referenced_major_id AND
t.[type] = N'U'
INNER JOIN sys.all_objects o ON(o.[type] = 'P' OR o.[type] = 'X' OR o.[type] = 'PC')
AND o.[object_id] = sd.[object_id]
SELECT * FROM sys.all_objects
WHERE([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name]
update statistics tblname with fullscan
查看存储过程依赖的表
最新推荐文章于 2022-07-28 15:28:43 发布