呵呵 童年

版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明
http://wr112.blogbus.com/logs/65083860.html

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'

     &nbs