Get dependant Objects

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Abhijit » Get dependant Objects

Get dependant Objects

Rate Topic

Display Mode

Topic Options

Author

Message

abmore

Posted Tuesday, October 02, 2007 10:18 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 1,134, Visits: 818

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

Post #406022

Derek Dongray

Posted Tuesday, November 27, 2007 2:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

fahey.jonathan

Posted Friday, August 24, 2012 9:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 11, 2013 5:58 PM
Points: 100, Visits: 347

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 »

Permissions