Get dependant Objects

• abmore

  Hall of Fame

  Points: 3628

  More actions

  October 2, 2007 at 10:18 pm

  #375094

  Comments posted to this topic are about the item Get dependant Objects

• StarNamer

  SSCrazy Eights

  Points: 8633

  More actions

  November 27, 2007 at 2:58 am

  #754443

  I'd be interested if anyone could come up with a way to make SYSDEPENDS reliable.

  Try the following:

  create table a (id int)

  go

  create procedure b as begin select id from a end

  go

  SELECT O.id, O.name as P1, O2.name as P2, D.depid, D.depnumber

  FROM sysdepends D

  INNER JOIN sysobjects O ON O.id = D.id

  INNER JOIN sysobjects O2 ON O2.id = D.depid

  where o.name='b'

  go

  exec b

  go

  drop table a

  go

  create table a (id int)

  go

  SELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumber

  FROM sysdepends D

  INNER JOIN sysobjects O ON O.id = D.id

  INNER JOIN sysobjects O2 ON O2.id = D.depid

  where o.name='b'

  go

  exec b

  go

  SELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumber

  FROM sysdepends D

  INNER JOIN sysobjects O ON O.id = D.id

  INNER JOIN sysobjects O2 ON O2.id = D.depid

  where o.name='b'

  go

  drop table a

  drop procedure b

  go

  The message output will be:

  (1 row(s) affected)

  (0 row(s) affected)

  (0 row(s) affected)

  (0 row(s) affected)

  (0 row(s) affected)

  The output indicates that SYSDEPENDS has no information on what B depends on, although it still executes OK and hence clearly depends on the recreated table A!

  Derek

• fahey.jonathan

  Hall of Fame

  Points: 3564

  More actions

  August 24, 2012 at 9:39 am

  #1529820

  Try this. It refreshes the data in sysdepends. I don't guarantee that all necessary objects are covered, just the ones I was using when I wrote this.

  Yes, I know it uses a cursor and I could generate a string with the EXEC statements and execute that, but I don't see a big difference. Either way, I am executing the statements one at a time.

  SELECT GETDATE()

  DECLARE @Name VARCHAR(100), @Type CHAR(5)

  DECLARE ModList

  CURSOR FAST_FORWARD READ_ONLY FOR

  SELECT xType, Name

  FROM sys.sysobjects

  WHERE xType IN ('P', 'FN', 'TR', 'V', 'TF')

  AND Name NOT LIKE 'dt~_%' ESCAPE '~'

  AND Name NOT LIKE 'jtf%'

  AND Name NOT LIKE 'x~_%' ESCAPE '~'

  --AND Name NOT IN ('...', '...')

  ORDER BY Name

  OPEN ModList

  FETCH NEXT

  FROM ModList

  INTO @Type, @Name

  WHILE @@FETCH_STATUS = 0

  BEGIN -- ModList Cursor

  PRINT @Type + @Name

  IF @Type = 'V'

  EXEC sp_refreshview @Name

  ELSE

  EXEC sp_refreshsqlmodule @Name

  FETCH NEXT

  FROM ModList

  INTO @Type, @Name

  END -- ModList Cursor

  CLOSE ModList

  DEALLOCATE ModList

  SELECT GETDATE()

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 10, 2016 at 7:01 am

  #1877762

  Thanks for the script.