Hi,
i am trying to find the list of dependent object for stored prcedure. I am not able to locate depenedency for sp that has temp table in sql statement.
i am trying to explain the scenerio by following examples
e.g--use northwind
Create proc testabc
as
BEGIN
create table #emp(fname varchar(50))
insert into #emp select firstname from employees
select * from #emp
drop table #emp
END
in above procedure employees table is used, but sp_depends didnot return any dependency.
2 example use northwind
Create proc testabc2
as
BEGIN
select firstname into #emp from employees
select employees.firstname,emptype.type into #tmp1
from #emp join employees on #emp.firstname=employees.firstname
join emptype on employees.employeeid=emptype.empid
select * from #tmp1
drop table #emp
drop table #tmp1
END
in above proc sysdepends has only one entry for employees table, but no enrty for emptype table.
Is there any way so that i could get the list all table and columns used in procedure like above
thnx in advance
lalit
getting depedency list from sysdepends
ChrisBradley
lalit,
Try the following. Use the name of the object after the like clause. This may take awhile to run depending on the size of syscomments
select
object_Name(id)from syscomments
where
text like '%[#]temp%'Hope this helps!
Ron
shads
eyal k