sp_depends 查找表的依奈项-优快云博客

本文提供了一种在SQL Server 2000及2005中查找存储过程所依赖的表的方法。通过使用`sp_depends`及编写特定的查询脚本来实现这一目标。

1、sp_depends '对象名'

2、脚本的方式来查找
--------------------------------------------------------------------------------
-- for sql server 2000
--------------------------------------------------------------------------------
declare @procedure_name nvarchar(255)
    set @procedure_name = N'CustOrderHist'

select distinct
       procedure_name = object_name(c.id)
      ,table_name      = t.name
from dbo.syscomments c
          inner join (select name from dbo.sysobjects where xtype='U') t
    on (   c.text like '% '   + t.name + ' %'
        or c.text like '%.'   + t.name + ' %'
        or c.text like '% ![' + t.name + '!] %' escape '!'
        or c.text like '%.![' + t.name + '!] %' escape '!'
       )
where c.id = object_id(@procedure_name)

--------------------------------------------------------------------------------
-- for sql server 2005
--------------------------------------------------------------------------------
declare @procedure_name nvarchar(255)
    set @procedure_name = N'CustOrderHist'

select distinct
       procedure_name = object_name(c.id)
      ,table_name      = t.name
from sys.syscomments c
          inner join sys.tables t
    on (   c.text like '% '   + t.name + ' %'
        or c.text like '%.'   + t.name + ' %'
        or c.text like '% ![' + t.name + '!] %' escape '!'
        or c.text like '%.![' + t.name + '!] %' escape '!'
       )
where c.id = object_id(@procedure_name)

转载于:https://www.cnblogs.com/JohnXIe/archive/2008/07/17/1245276.html