SQLServerCentral / Article Discussions / Discuss content posted by Abhijit / Article Discussions by Author / Get dependant Objects / Latest Posts

RE: Get dependant Objects

fahey.jonathan — Fri, 24 Aug 2012 09:39:22 GMT

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.[code="sql"]SELECT GETDATE()DECLARE @Name VARCHAR(100), @Type CHAR(5)DECLARE ModListCURSOR 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 NameOPEN ModListFETCH NEXTFROM ModListINTO @Type, @NameWHILE @@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 CursorCLOSE ModListDEALLOCATE ModListSELECT GETDATE()[/code]

RE: Get dependant Objects

Derek Dongray — Tue, 27 Nov 2007 02:58:43 GMT

I'd be interested if anyone could come up with a way to make SYSDEPENDS reliable.Try the following:[quote]create table a (id int)gocreate procedure b as begin select id from a endgoSELECT O.id, O.name as P1, O2.name as P2, D.depid, D.depnumberFROM sysdepends DINNER JOIN sysobjects O ON O.id = D.idINNER JOIN sysobjects O2 ON O2.id = D.depidwhere o.name='b'goexec bgodrop table agocreate table a (id int)goSELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumberFROM sysdepends DINNER JOIN sysobjects O ON O.id = D.idINNER JOIN sysobjects O2 ON O2.id = D.depidwhere o.name='b'goexec bgoSELECT O.id, O.name as P1, O2.name as p2, D.depid, D.depnumberFROM sysdepends DINNER JOIN sysobjects O ON O.id = D.idINNER JOIN sysobjects O2 ON O2.id = D.depidwhere o.name='b'godrop table adrop procedure bgo[/quote]The message output will be:[quote](1 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)[/quote]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!

Get dependant Objects

abmore — Tue, 02 Oct 2007 22:18:30 GMT

Comments posted to this topic are about the item [B]Get dependant Objects[/B]