Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SysTable info

Posted on 2006-03-28
9
Medium Priority
?
583 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
0
Comment
Question by:AlexPonnath
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16309910
Hi AlexPonnath,
There is no such table.. the syscomments table holds the sp code..

Cheers!
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 1000 total points
ID: 16310684
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16310695
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16310853
Whta's the benefit of going like that.. You can easily identify ypur return value from the procedure itself ...
0
 

Author Comment

by:AlexPonnath
ID: 16312448
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16321654
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question