How do I retrieve the Select statement from a Procedure

[FancyPrairie]

I'm trying to determine the fields within the Select statement of a stored procedure. I've done it with a view by adding the Where clause 1=2 to it (i.e. Select * from Query1 Where 1=2). Although it does not return any records, I can open the recordset and loop through the fields to get their names.

But I'm not sure how to do this with a stored procedure. I figured if I could extract the Select statement from the procedure then I could do what needs to be done. I tried opening a recordset like this: rst.Open "sp_helptext 'storedprocedurename'...

Although I get the select statement, I get the entire definition of the procedure, which could get rough trying to strip out everything but the Select statement.

So, is there some way to extract the Select statement from a stored procedure? Or, better yet, retrieve the names of the fields within the Select statement?

 

[PWise]

for f in rst.fields
debug.print rst(f).name
next

 

[FancyPrairie]

pwise,

I think what you gave me is after the fact. That is, suppose the stored procedure returned 10,000 records. Wouldn't I have to wait for the result set to finish before I could execute your code? Assuming that's the case, that's why I need to add the Where clause 1=2, so I don't get back a set of records. Am I thinking correctly?

Basically, once I get the result set back, then I can do what you suggested.

 

[gmmastros]

You cannot get this information from SQL Server. You can get the parameters for a stored procedure, and the column list from a view, etc... but you cannot get an output list of columns from a stored procedure without actually running the procedure and examining the columns.

You see... a stored procedure could return multiple recordset, or it could return a completely different recordset based on your data (or any other criteria you decide). Because of the flexibility with stored procedures, you cannot query the system to determine the output columns.

make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[FancyPrairie]

For anyone that is interested, this is how I got it to work.

As to the subject of my post (how to retrieve the Select statement of a procedure), you can call the procedure sp_helptext to retrieve the text within the procedure then you can search for the Select statement.

However, as stated in my original post, I needed to know the column names the stored procedure would output.

To list all of the columns of a stored procedure, do this:

        cnn.ConnectionString = "blah blah blah"
        cnn.Open

        '**************************************************
        '* Get number of parameters defined in procedure  *
        '**************************************************

        rst.Open ("SELECT Count(*) FROM Information_Schema.Parameters WHERE specific_name='YourStoredProcedureName'", cnn)
        k = rst.Fields(0).Value
        rst.close

        '*************************************************
        '*  Build SQL statement to retrive column names  *
        '*  Let "null" represent each expected parameter *
        '*************************************************

        strSQL = "set fmtonly on exec YourStoredProcedureName "

        if (k > 0) then
            for i = 1 to k
                if (i=1) then strSQL = strSQL & " null" else strSQL = strSQL & ", null"
            next
         end if

        strSQL = strSQL & " set fmtonly off"

        '********************************
        '*  Loop thru the column names  *
        '********************************

        rst.Open (strSQL,cnn)

        for i = 0 to rst.Fields.Count - 1
            msgbox(rst.Fields(i).Name)
        Next

        rst.Close
        cnn.Close

To list all of the columns in a table or view, use the stored procedure sp_columns.

Example: exec sp_columns YourTableName