查找存储过程中使用的字段及库表等

windy8848

--查找存储过程、函数中使用的字段及库表等
declare @id int
declare @procCounts int
declare @key varchar(30)
declare @procname char(60)
set @id=1
set @key='spkfk'
create table #procList
(id int,
procName char(60)
)
create table #procText
(procname char(60),
sqls varchar(1000)
)
insert into #procList
select ROW_NUMBER() over(order by name) id, name as procName
from sys.objects where type in ('P','FN') and is_ms_shipped=0
select @procCounts=count(id) from #procList
while @id<@procCounts
begin
select @procname=procname from #procList where id=@id
insert into #procText
(sqls)
exec sp_helptext @procname
update #proctext set procname=@procname where procname is null
set @id=@id+1
end;
select distinct rtrim(procname)
from #proctext
where sqls like '%'+@key+'%'
drop table #proctext
drop table #procList

davids

很详细，很实用，谢谢版主分享！