Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get dependant Objects Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2007 10:18 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
Comments posted to this topic are about the item Get dependant Objects
Post #406022
Posted Tuesday, November 27, 2007 2:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #426243
Posted Friday, August 24, 2012 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:56 PM
Points: 106, Visits: 367
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()

Post #1349767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse