Get dependant Objects

  • abmore

    Hall of Fame

    Points: 3628

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

  • StarNamer

    SSCrazy Eights

    Points: 8633

    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

    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

    Thanks for the script.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply