find depended objects - SQL, database, informix, oracle, SQL Server

find depended objects

	Print
	Email

Submitted on: 11/19/2003 6:17:40 AM
By: chandra sekhar
Level: Advanced
User Rating:

By 1 Users
Compatibility:SQL Server 2000, SQL Server 7.0

Users have accessed this code 771 times.

(About the author)

handle - cr's you cant find all the depended objects by using sp_depends ,if you are execute a string which contains a table inside a stored procedure, but this function will give you a list of objects

code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!

Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.


--**************************************
--     
-- Name: find depended objects
-- Description:handle - cr's 
you cant find ALL the depended objects BY using sp_depends ,if you are EXECUTE a string which CONTAINS a TABLE inside a stored procedure, but this function will give you a list OF objects
-- By: chandra sekhar
--
-- Inputs:table name
--
-- Returns:depended objects
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=784&lngWId;=5--for details.--**************************************
--     

--- ONLY FOR THE MAINTANANCE PURPOSE
-- FOR TO FIND THE OBJECTS WHICH ARE BEL
--     ONGS TO A PARTICULAR TABLE 
--automize Cr`s and save time for the CR
--     's
/*
	FOR TO handle the cr`s IF you just think how to handle the cr`s THEN you can 
	SAVE a lot OF time
	FOR example -- there IS a cr OF which you have TO modify 2 tables named TBL_ONE & TBL_TWO
	FOR that the steps required are 
			step 1: find out what are the objects belongs TO this TBL_ONE AND modify
			step 2: find out what are the objects belongs TO this TBL_TWO AND modify
	JUST THINK ANOTHER WAY
			step1: find out what are the objects common FOR both TBL_ONE & TBL_TWO AND modify
			step2 : find out what are the objects belongs TO this TBL_ONE AND modify
			step3: find out what are the objects belongs TO this TBL_TWO AND modify
THE BELOW PROCEDURE ALSO HAVING A FUNCTION WILL TELLS YOU HOW TO FOLLOW THE STEPS FOR TO HANDLE THE CR
SELECT * FROM fnGetObjects('TBL_ONE % TBL_TWO')
SELECT * FROM fnGetObjects('TBL_ONE')
SELECT * FROM fnGetObjects('TBL_TWO')
						----- p.c sekhar 
*/
CREATE function fnGetObjects(@tableNames varchar(255))
returns TABLE 
as
return(
	SELECT TOP 1000 name FROM sysobjects s JOIN syscomments c on s.id = c.id 
	WHERE xtype in('p','v','TR') AND s.status > 1 and c.text LIKE '%' + @tableNames +'%'
	ORDER BY refdate
	)

Other 2 submission(s) by this author

Report Bad Submission

Your Vote!

Other User Comments

11/20/2003 6:58:00 AM:Christina McEntire

I like this function for being able to 
return a list of object dependencies 
that two tables have in common. You 
might want to return the object type, 
so that it's easier to find the object 
to update it: 
CREATE function 
fnGetObjects(@tableNames 
varchar(255))
returns table 
as
return(
	select top 1000 
name
	, CASE xtype 
		WHEN 'p' THEN 
'sproc'
		WHEN 'v' THEN 'view'
		WHEN 
'tr' THEN 'trigger'
	  END As 
Type
	from sysobjects s join 
syscomments c on s.id = c.id 
	where 
xtype in('p','v','TR') and s.status > 1 
and c.text like '%' + @tableNames 
+'%'
	order by refdate
	)

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular code, please click here.