查看表被那些存储过程、函数、视图、触发器使用

1.系统自带 的sp_depends

sp_depends
显示有关数据库对象相关性的信息（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图）。不报告对当前数据库以外对象的引用。

语法
sp_depends [ @objname = ] 'object'

参数
[@objname =] 'object'

被检查相关性的数据库对象。对象可以是表、视图、存储过程或触发器

 

如果sp_depends 'TableName'  只带出'51笔'数据

 

那么另一种自定义可带出 '69笔'数据,   这多少笔数据由各表所决定

2.自定义 usp_depends

 

create procedure dbo.usp_depends
(
   @object_name nvarchar(255)
)
as
begin
   set nocount on

   -- check input
   if @object_name like '%.%'
      set @object_name = substring(@object_name, charindex('.', @object_name) + 1, 255)

   set @object_name = ltrim(rtrim(@object_name))

   -----------------------------------------------------------------------------
   -- find objects which depends @object_name
   -----------------------------------------------------------------------------
   -- create temp table
   create table #depend_by (obj nvarchar(255), objtype nvarchar(255), is_system_find bit null)

   -- get : use dbo.sysdepends
   insert into #depend_by(obj, objtype, is_system_find)
   select obj, objtype, is_system_find
     from (select distinct
                  replace(object_name(id), 'dbo.', '') as obj,
                  case
                     when 1= objectproperty(id, 'IsProcedure')
                         then 'stored procedure'
                     when 1= objectproperty(id, 'IsInlineFunction')
                         then 'function'
                     when 1= objectproperty(id, 'IsScalarFunction')
                         then 'function'
                     when 1= objectproperty(id, 'IsTableFunction')
                         then 'function'
                     when 1= objectproperty(id, 'IsTrigger')
                         then 'trigger'
                     when 1= objectproperty(id, 'IsView')
                         then 'view'
                     else ''
                  end as objtype,
                  1 as is_system_find
             from dbo.sysdepends
            where depid = object_id(@object_name)
          ) t
      where not exists (select 1 from #depend_by where obj = t.obj)

   -- get : use custom query from 'syscomments'
   insert into #depend_by(obj, objtype, is_system_find)
   select obj, objtype, 0
     from (select distinct
                  obj         = object_name(c.id)
                 ,objtype     = case
                                   when 1= objectproperty(c.id, 'IsProcedure')
                                       then 'stored procedure'
                                   when 1= objectproperty(c.id, 'IsInlineFunction')
                                       then 'function'
                                   when 1= objectproperty(c.id, 'IsScalarFunction')
                                       then 'function'
                                   when 1= objectproperty(c.id, 'IsTableFunction')
                                       then 'function'
                                   when 1= objectproperty(c.id, 'IsTrigger')
                                       then 'trigger'
                                   when 1= objectproperty(c.id, 'IsView')
                                       then 'view'
                                   else ''
                                end
             from dbo.syscomments c
                     inner join (select name from dbo.sysobjects where name=@object_name) o
               on (   c.text like '%[( ]'    + o.name   + ' %'
                   or c.text like '%[( ]'    + o.name   + char(13)+char(10) + '%'
                   or c.text like '%[( ]"'   + o.name   + '" %'
                   or c.text like '%[( ]"'   + o.name   + '"' + char(13)+char(10) + '%'
                   or c.text like '%.'       + o.name   + ' %'
                   or c.text like '%."'      + o.name   + '" %'
                   or c.text like '%.'       + o.name   + char(13)+char(10) + '%'
                   or c.text like '%."'      + o.name   + '"' + char(13)+char(10) + '%'
                   or c.text like '%[( ]!['  + o.name   + '!] %' escape '!'
                   or c.text like '%[( ]!['  + o.name   + '!]' + char(13)+char(10) + '%' escape '!'
                   or c.text like '%.!['     + o.name   + '!] %' escape '!'
                   or c.text like '%.!['     + o.name   + '!]' + char(13)+char(10) + '%' escape '!'
                  )
           ) t
       where not exists (select 1 from #depend_by where obj = t.obj)
         and obj <> @object_name

   -- output results
   select obj as object_depend_by, objtype as type, is_system_find
     from #depend_by order by objtype, obj

   -- free resource
   truncate table #depend_by
   drop table #depend_by

   -----------------------------------------------------------------------------
   -- find objects depend by @object_name
   -----------------------------------------------------------------------------
   if 1 = objectproperty(object_id(@object_name), 'IsTable')
      return

   -- create temp table
   create table #depend (obj nvarchar(255), objtype nvarchar(255), is_system_find bit null)

   -- get : use dbo.sysdepends
   insert into #depend (obj, objtype, is_system_find)
   select obj, objtype, is_system_find
     from (select distinct
                  replace(object_name(depid), 'dbo.', '') as obj,
                  case
                     when 1= objectproperty(depid, 'IsProcedure')
                         then 'stored procedure'
                     when 1= objectproperty(depid, 'IsInlineFunction')
                         then 'function'
                     when 1= objectproperty(depid, 'IsScalarFunction')
                         then 'function'
                     when 1= objectproperty(depid, 'IsTableFunction')
                         then 'function'
                     when 1= objectproperty(depid, 'IsTrigger')
                         then 'trigger'
                     when 1= objectproperty(depid, 'IsView')
                         then 'view'
                     when 1= objectproperty(depid, 'IsTable')
                         then 'table'
                     else ''
                  end as objtype,
                  1 as is_system_find
             from dbo.sysdepends
            where id = object_id(@object_name)
          ) t
      where not exists (select 1 from #depend where obj = t.obj)

   -- get : use custom query from 'syscomments'
   insert into #depend (obj, objtype, is_system_find)
   select obj, objtype, 0
     from (select distinct
                  obj         = o.name
                 ,objtype     = case
                                   when 1= objectproperty(o.id, 'IsProcedure')
                                       then 'stored procedure'
                                   when 1= objectproperty(o.id, 'IsInlineFunction')
                                       then 'function'
                                   when 1= objectproperty(o.id, 'IsScalarFunction')
                                       then 'function'
                                   when 1= objectproperty(o.id, 'IsTableFunction')
                                       then 'function'
                                   when 1= objectproperty(o.id, 'IsTrigger')
                                       then 'trigger'
                                   when 1= objectproperty(o.id, 'IsView')
                                       then 'view'
                                   when 1= objectproperty(o.id, 'IsTable')
                                       then 'table'
                                   else ''
                                end
             from (select * from dbo.syscomments where id = object_id(@object_name)) c
                     inner join dbo.sysobjects o
               on (   c.text like '%[( ]'    + o.name  + ' %'
                   or c.text like '%[( ]'    + o.name  + char(13)+char(10) + '%'
                   or c.text like '%[( ]"'   + o.name  + '" %'
                   or c.text like '%[( ]"'   + o.name  + '"' + char(13)+char(10) + '%'
                   or c.text like '%.'       + o.name  + ' %'
                   or c.text like '%."'      + o.name  + '" %'
                   or c.text like '%.'       + o.name  + char(13)+char(10) + '%'
                   or c.text like '%."'      + o.name  + '"' + char(13)+char(10) + '%'
                   or c.text like '%[( ]!['  + o.name  + '!] %' escape '!'
                   or c.text like '%[( ]!['  + o.name  + '!]' + char(13)+char(10) + '%' escape '!'
                   or c.text like '%.!['     + o.name  + '!] %' escape '!'
                   or c.text like '%.!['     + o.name  + '!]' + char(13)+char(10) + '%' escape '!'
                  )
           ) t
       where not exists (select 1 from #depend where obj = t.obj)
         and obj <> @object_name

   -- output results
   select obj as objects_depend, objtype as type, is_system_find from #depend order by objtype, obj

   -- free resource
   truncate table #depend
   drop table #depend
end
go