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)
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)