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