--TRY select sc.text,sj.name from syscomments sc ,sysobjects sj where patindex('%'+sj.name+'%',sc.text)>0 and sj.type='P'
--TRY select sc.text,sj.name from syscomments sc ,sysobjects sj where patindex('%'+sj.name+'%',sc.text)>0 and sj.type='U'
--try:create proc Get_pname @tablename varchar(10) --要查询的表名 as declare @name varchar(50),@pros varchar(1000) create table ##(txt varchar(1000)) set @pros='' declare wsp cursor for select name from sysobjects where xtype='p' and uid=1 and category=0 open wsp fetch wsp into @name while(@@fetch_status=0) begin exec('insert into ## exec sp_helptext '+@name) if exists(select 1 from ## where ' '+txt+' ' like '% '+@tablename+' %') set @pros=@pros+@name+',' fetch wsp into @name end close wsp deallocate wsp select @pros go
--or: create proc wsp @tablename varchar(50) as select sj.name from syscomments sc ,sysobjects sj where sj.type='P' and sc.id=sj.id and ' '+sc.text+' ' like '% '+@tablname+' %' and sj.uid=1 and sj.category=0 go
这个没有好办法。只能从定义里找from了。
select sc.text,sj.name from syscomments sc ,sysobjects sj
where patindex('%'+sj.name+'%',sc.text)>0 and sj.type='P'
select sc.text,sj.name from syscomments sc ,sysobjects sj
where patindex('%'+sj.name+'%',sc.text)>0 and sj.type='U'
--try:create proc Get_pname
@tablename varchar(10) --要查询的表名
as
declare @name varchar(50),@pros varchar(1000)
create table ##(txt varchar(1000))
set @pros=''
declare wsp cursor for select name from sysobjects where xtype='p' and uid=1 and category=0
open wsp
fetch wsp into @name
while(@@fetch_status=0)
begin
exec('insert into ## exec sp_helptext '+@name)
if exists(select 1 from ## where ' '+txt+' ' like '% '+@tablename+' %')
set @pros=@pros+@name+','
fetch wsp into @name
end
close wsp
deallocate wsp
select @pros
go
--or:
create proc wsp
@tablename varchar(50)
as
select sj.name from syscomments sc ,sysobjects sj
where sj.type='P' and sc.id=sj.id and ' '+sc.text+' ' like '% '+@tablname+' %'
and sj.uid=1 and sj.category=0
go