Getting Columns from SP - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting Columns from SP

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-10-07 : 14:54:04

What query can i use to know that a particular field is used in which all stored procedure.Example..emp_id is one field that I want to know in which all stored procedure its being used.Is there any query I can use.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-07 : 16:56:12

for current database try this:

select	distinct routine_name 
from	information_schema.routines 
where	routine_definition like '%emp_id%'

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 02:21:01

select	distinct routine_name 
from	information_schema.routines 
where	routine_definition like '% emp_id %'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 04:37:18

You're probably going to have to allow for a bunch of separators aren't you? Something along the lines of:

routine_definition like '%[ \[.]emp_id[ \][.' + CHAR(13) + ']%' ESCAPE '\'

Kristen

anuj164
Starting Member

49 Posts

Posted - 2005-10-11 : 17:01:25

personally I don't think so, we should be fine with

select distinct routine_name
from information_schema.routines
where routine_definition like '%emp_id%'

Kristen why you think to include separators; any specific thing that you have come across.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-11 : 17:44:36

quote:
Originally posted by madhivanan

or

select distinct routine_name
from information_schema.routines
where routine_definition like '% emp_id %'

Madhivanan

Failing to plan is Planning to fail

How is this different than TG's? TG's covers yours as well as the case where there aren't spaces.

sqllearner,
I think you'll need square brackets around the underscore as underscore is a wild card character.

select distinct routine_name
from information_schema.routines
where routine_definition like '%emp[_]id%'

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 02:09:46

>>How is this different than TG's? TG's covers yours as well as the case where there aren't spaces.

Well

If sp has column named temp_id, then using '%emp_id%' will give that sp name
whereas '% emp_id %' will prevent it

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 02:22:27

True, but now it won't grab "emp_id," which is what you'd have in the SELECT list. I guess you'd need multiple statements in the WHERE to catch everything.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources