using the output of sys_depends

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  July 30, 2009 at 1:00 am

  #208802

  Hi,

  i would like to use the output of sys.depends in a query

  i like to get only the distinct names from the sys.depends and store it in a table

  how can i do it?

  thanks,

  regards

  Viji

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  July 30, 2009 at 4:51 am

  #1032275

  Hi,

  i found the solution

  declare @SPName Varchar(255)

  DECLARE CsrProcName CURSOR FOR

  SELECT NAME

  FROM sys.objects

  WHERE type IN ('FN', 'IF', 'TF','P')

  OPEN CsrProcName

  FETCH NEXT FROM CsrProcName INTO @SPName

  PRINT @SPName

  WHILE @@FETCH_STATUS =0

  BEGIN

  insert #tmp

  exec sp_depends @SPName

  FETCH NEXT FROM CsrProcName INTO @SPName

  PRINT @SPName

  END

  SELECT DISTINCT NAME FROM #TMP WHERE TYPE ='user table'

  CLOSE CsrProcName

  DEALLOCATE CsrProcName

  select distinct name from #tmp where type ='user table'

  still one more check point if it returns trigger then it returns only two columns then itz failing raising error. i'm working on it

  thanks

  regards

  vg