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

来源：互联网发布：linux git生成ssh key 编辑：程序博客网时间：2024/04/30 03:43
在数据库维护时, 有时需查询数据库对象(如表,视图,存储过程等)的关联(即被参考)数据库对象, 可使用如下自定义存储过程查询.
if exists(select 1 from sys.objects where type='P' and nam='spFindObject')   drop proc dbo.spFindObjectgocreate procedure dbo.spFindObject(@template varchar(200))asbegin 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<>@templateupdate 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 descend
0 0