Need sp_columns/Information_Schema.Columns equivalent for stored procs

[SBendBuckeye]

Hello,

I need to determine the columns in a stored procedure on the fly. Is there an equivalent to sp_columns or Information_Schema.Columns? The stored procedures in question are used to populate master table comboboxes but can have additional columns in the query (eg Select * From Employees). Thanks for any ideas and/or suggestions!



Have a great day!

j2consulting@yahoo.com

 

[NoCoolHandle]

To the best of my knowledge there is no way in Sql without some serious hacking to find out what colums exist in a storedproc.

The reason is that table and view data lists it's columns in the syscolumns table and this is where the informationschema views and sp_columns get there data from.

The only place procs exist is in the syscomments (and sysobjects) table. The create statement exists there and you would have to do some serious parcing of logic to ensure you were returning the columns that the proc did.

My Sugestion would be to loop through your recordset back in your client and that could be used to set your column headdings.

Ie In classic ADO..
(rs = a recordset object and cn = a connection object)

rs.open "someproc paramlist",cn
dim x as integer
for x = 0 to rs.fields.count - 1
  msgbox rs.fields(x).name
next

In the above example you can easily get the number of cols
Rs.fields.count and the name property of the ordinal position of the column gives it's name..

Bottom line.. The only way I know of to get column names for a proc is to execute it and then loop through the cols.

HTH


Rob

 

[JamesLean]

Good explanation Rob, I'd back that up.

--James

 

[SQLBill]

One purpose of Stored Procedures is to hide the commands from the end user. It's similar to a View in that regards. You might say, "But the end user sees the column names in the returned data." Well, not really. In the stored procedure you can 'Alias' the column names so the end user doesn't know the true names. This is used for security purposes.

-SQLBill

Posting advice: FAQ481-4875

 

[SBendBuckeye]

The information is available in sp_helptext, you just have to parse it out. I can do that fairly easily in VB6 or .Net but I was hoping there was a way to do it in SQL Server without having to go outside of the environment.



Have a great day!

j2consulting@yahoo.com

 

[NoCoolHandle]

SBendBuckeye..

YES there is a way, you just need to write the code. However I stand by what I said earlier, you will need to parse the results and work out which columns are just intermediate and which are final output. THat of course will depend on your knowledge of sql and the ability to workout the results of case statemnts and if statemns based on parameters that you don't know about yet...

BOTTOM LINE.. This is best done at the client..

Sp_help does nothing more than query the text column of the syscomments table (with a order to ensuer you don't get the build statement in the wrong order)

I have written a program that looks at other programs and based on code color codes keywords.. Crossreferences calls to other functions and makes sure that the comments are colored correctly (and doens't keyword color commented keywords)

SOunds simple, but it can be a pain to track where you are in a loop. And althought sql supports this type of logic. It is just plain better to do this at the client.

If you are worried about returning a couple of hunderd thouand rows just to get the col names then try using
[blue]set rowcount=1[/blue] before you execute the proc. THis will limit your return and give you cols quickly and easliy (only 1 row will ever come back and indexes might be used that could get results fast) you will more likley than not need to execute the proc with the recomplie option to make sure you don't generate a query plan that would be inappropriate for normal executions..

Rob
HAVE A GREAT DAY