Latest Code Ticker for SQL
Daily Code Email
|Submitted on: 11/19/2003 6:17:40 AM |
By: chandra sekhar
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|
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://
-- 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
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))
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|
See Voting Log |
|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:
select top 1000
, CASE xtype
WHEN 'p' THEN
WHEN 'v' THEN 'view'
'tr' THEN 'trigger'
from sysobjects s join
syscomments c on s.id = c.id
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.