SysTable info

Which systable holds information on which fields will be returned by a given stored procedure ?
I can find the parameter fields but have no luck finding the returned fields..

Alex
AlexPonnathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hi AlexPonnath,
There is no such table.. the syscomments table holds the sp code..

Cheers!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptjcbCommented:
No table exists like that. As aneeshatingal mentions there script for the stored procedure is kept in syscomments (for SQL Server 2000). You could use something like:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%column name%'
    AND ROUTINE_TYPE='PROCEDURE'

to return what the procedure would be. If you query the syscomments you will notice that SQL has an 8,000 character limit. Therefore, you might have to add several together. Something like this:

SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%column name%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1

It is different in 2005.
    GROUP BY OBJECT_NAME(id)

0
ptjcbCommented:
Sorry, my cut-and-paste skills are showing...

SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%column name%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aneesh RetnakaranDatabase AdministratorCommented:
Whta's the benefit of going like that.. You can easily identify ypur return value from the procedure itself ...
0
AlexPonnathAuthor Commented:
Your below query returns the stored procedure name but that still doesnt help me much since i knew the id before

SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%column name%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)

so my problem is that there seems to be no easy way to parse out the column names from the stored procedure.
Is there another way to do this ?
0
ptjcbCommented:
No, there is no simple way to do that as far as I know. Your best bet is what aneeshattingal mentioned - open each stored procedures to see what it does.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.