本文由 www.sqlstudy.com 原创，版权所有，转载请注明作者和出处！

本文链接：http://www.sqlstudy.com/sql_article.php?id=2008071002

SQL Server：怎样知道一个表被哪些存储过程、函数、视图、触发器使用？（SQL Server 2000 & 2005）

今天上面给了这样一个任务：给一个表名，如何知道哪些存储过程或者函数引用过该表？对于类似这样的 object 依赖的问题，常见的解决办法是使用 SQL Server 的系统过程 sp_depends。但是 sp_depends 不太可靠，那么还有其他的办法没？哎呀，啥也不说了。具体思路，看下面的对话 ：）

[4:47:50 PM] HK :manager 说： hi p.c.w.l

[4:48:11 PM] HK :manager 说： given a table, how do we know which function or 

                              stored procedure use the table?

[4:48:31 PM] p.c.w.l 说： wait...

[4:53:46 PM] p.c.w.l 说： -- pls try

declare @table_name nvarchar(255)

    set @table_name = N'Orders'

select distinct

       container_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 t.name = @table_name

  order by 1

[4:55:32 PM] p.c.w.l 说： you can use, exec sp_depends 'Orders'

[4:55:49 PM] p.c.w.l 说： 但是 sp_depends 是不太可靠的。

[4:56:07 PM] HK :manager 说： yeah I find sp_denpends not so reliable.

[4:56:24 PM] p.c.w.l 说： 你可以综合上面两种方法

[4:56:28 PM] HK :manager 说： Thanks this could be very useful.

[4:56:38 PM] p.c.w.l 说： :)

[4:56:53 PM] HK :manager 说： can u write a procedure that 综合上面两种方法?

[4:57:03 PM] p.c.w.l 说： ok

[4:57:14 PM] HK :manager 说： I want to run this on a sql server 2000 though.

[4:57:31 PM] HK :manager 说： not sure if sql server 2000 has the same sys.* table

--------------------------------------------------------------------------------

-- author : p.c.w.l

-- source : www.sqlstudy.com

-- create : 2008-01-01

-- descr  : given a table, get functions or stored procedures ... use the table

--------------------------------------------------------------------------------

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

类似的要求，在文章 SQL Server：获取一个存储过程中依赖的所有表（SQL Server 2000 & 2005） 就已经研究过。所以，很快地，就完成了上面下达的任务：）