查找数据库对象的关联对象列表

    在数据库维护时, 有时需查询数据库对象(如表,视图,存储过程等)的关联(即被参考)数据库对象, 可使用如下自定义存储过程查询.

if exists(select 1 from sys.objects where type='P' and nam='spFindObject')
   drop proc dbo.spFindObject
go

create procedure dbo.spFindObject
(@template varchar(200))
as
begin
 set nocount on

 declare @r table(ObjectID int,
                              ObjectName varchar(100),
                              ObjectType varchar(10),
                              CreateDate datetime,
                              LastExecute datetime,     -- 最后执行时间
                              ExecuteCount int,            -- 总执行次数
                              ElapsedTime bigint,        -- 总执行时间
                              Operation varchar(50)     -- 操作类型
                             )
 
 insert into @r(ObjectID,ObjectName,ObjectType,CreateDate)
 select distinct b.object_id 'ObjectID',
                        b.name 'ObjectName',
                        b.type 'ObjectType',
                        b.create_date 'CreateDate'
   from sys.sql_expression_dependencies a
   inner join sys.objects b on a.referencing_id=b.object_id
   where a.referenced_id=object_id(@template)
   and b.name<>@template

update a
  set a.Operation=case when e.is_selected=1 or e.is_select_all=1 then 'SELECT, ' else '' end
                              +case when e.is_updated=1 then 'UPDATE, ' else '' end
  from @r a
  inner join (select c.object_id,
                               is_selected=cast(max(cast(c.is_selected as tinyint)) as bit),
                               is_select_all=cast(max(cast(c.is_select_all as tinyint)) as bit),
                               is_updated=cast(max(cast(c.is_updated as tinyint)) as bit)
                    from sys.sql_dependencies c
                    inner join sys.objects d on c.referenced_major_id=d.object_id
                    where d.name=@template
                    group by c.object_id) e on a.ObjectID=e.object_id

 update a
   set a.LastExecute=b.last_execution_time,
         a.ExecuteCount=b.execution_count,
         a.ElapsedTime=b.total_elapsed_time
   from @r a
   inner join (select  database_id,
                                 object_id,
                                 last_execution_time=max(last_execution_time),
                                 execution_count=sum(execution_count),
                                 total_elapsed_time=sum(total_elapsed_time)
                      from sys.dm_exec_procedure_stats
                      where type='P'
                      and database_id=(select top 1 dbid from sys.sysprocesses where spid=@@spid)
                      group by database_id,object_id) b on a.ObjectID=b.object_id
   where a.ObjectType='P'

 select ObjectName,ObjectType,CreateDate,LastExecute,ExecuteCount,ElapsedTime,Operation
   from @r
   order by case ObjectType when 'V' then 0 
                                                when 'P' then 1 
                                                else 2 end,ExecuteCount desc
end