FindOut Column Dependency in Various StoreProcedur

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FindOut Column Dependency in Various StoreProcedur

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 04:00:58

Hai All,

FindOut The Column Dependency In Various Store Procedures.
Actually My Reason is -> i want to rename a field.Then i want to know how many procedures it affects.Please give me a useful solution

Thanks & regrds
Riju A.O
KottSoftware

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 04:32:55

select Object_name(d3.Id) AS 'Sp','name' = (s6.name+ '.' + o1.name),

'column' = col_name(d3.depid, d3.depnumber)

from sys.sysobjects o1, sys.sysdepends d3, sys.sysusers s6

where o1.id = d3.depid
and o1.uid = s6.uid
and deptype < 2
and col_name(d3.depid, d3.depnumber) = 'your column name'
and (s6.name+ '.' + o1.name) = 'table name'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-12 : 04:35:23

Be aware : that won't work for any dynamic sql referencing the column changed in any of the stored procedures.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 04:41:01

raky that Not working

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 04:52:20

try this

select Object_name(d3.Id) AS 'Sp','name' = (s6.name+ '.' + o1.name),

'column' = col_name(d3.depid, d3.depnumber)

from sys.sysobjects o1, sys.sysdepends d3, sys.sysusers s6

where o1.id = d3.depid
and o1.uid = s6.uid
and deptype < 2
and col_name(d3.depid, d3.depnumber) = 'column name'
and o1.name = 'your table name'

Note : There is change in last stmt when compared with previous post

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 04:59:45

raky

Very Special thanks for you.Its too correct.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 05:07:54

My query will not show the list of sp's which are in other databases but using column which you are going to change. so be aware of it.

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 05:08:31

raky,
can you please illustrate about the query that you had sent.
This will be a value addition to the help that you had done.

thanks..

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 05:30:36

quote:
Originally posted by aoriju

raky,
can you please illustrate about the query that you had sent.
This will be a value addition to the help that you had done.

thanks..

In this query we are using sys.sysobjects which stores information about all objects in a database and sys.sysdepends holds information about which object in database depends on which object...and col_name is a function and read about it in books online

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 05:37:19

Ok Thanks raky....

aoriju
Posting Yak Master

156 Posts

Posted - 2008-12-12 : 05:47:22

raky,
one more help is needed...

is it possible to find the procedures, functions(scalar and tabular) which are using a particular text. ie, we will be commenting the name of the used page in the sql object..

so i want to get the dependant objects related to a particular text(it will be commented in the procedure or function)..

thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:10:59

also

http://blogs.msdn.com/rafats/archive/2007/04/11/column-dependencies.aspx

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-12 : 11:50:15

quote:
Originally posted by aoriju

raky,
one more help is needed...

is it possible to find the procedures, functions(scalar and tabular) which are using a particular text. ie, we will be commenting the name of the used page in the sql object..

so i want to get the dependant objects related to a particular text(it will be commented in the procedure or function)..

thanks in advance..

is this is you want ??

select distinct object_name(id) from sys.syscomments where text like '%yourtexthere%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources