October 2, 2007 at 10:18 pm
Comments posted to this topic are about the item Get dependant Objects
November 27, 2007 at 2:58 am
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
August 24, 2012 at 9:39 am
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.
SELECTGETDATE()
DECLARE@NameVARCHAR(100),@TypeCHAR(5)
DECLAREModList
CURSORFAST_FORWARD READ_ONLY FOR
SELECTxType, Name
FROMsys.sysobjects
WHERExType IN ('P', 'FN', 'TR', 'V', 'TF')
ANDName NOT LIKE 'dt~_%' ESCAPE '~'
ANDName NOT LIKE 'jtf%'
ANDName NOT LIKE 'x~_%' ESCAPE '~'
--ANDName NOT IN ('...', '...')
ORDER BY Name
OPENModList
FETCHNEXT
FROMModList
INTO@Type, @Name
WHILE@@FETCH_STATUS = 0
BEGIN-- ModList Cursor
PRINT@Type + @Name
IF@Type = 'V'
EXECsp_refreshview @Name
ELSE
EXECsp_refreshsqlmodule @Name
FETCHNEXT
FROMModList
INTO@Type, @Name
END-- ModList Cursor
CLOSEModList
DEALLOCATE ModList
SELECTGETDATE()
May 10, 2016 at 7:01 am
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