We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SysTable info

AlexPonnath
AlexPonnath asked
on
Medium Priority
598 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Hi AlexPonnath,
There is no such table.. the syscomments table holds the sp code..

Cheers!

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
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)

Commented:
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)
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Whta's the benefit of going like that.. You can easily identify ypur return value from the procedure itself ...

Author

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 ?

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.