Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Apollois
Starting Member
49 Posts |
 Posted - 2004-06-17 : 12:08:38
|
| How can I obtain a list of columns (and column properties) that are output by a stored procedure?I'm looking for something like sp_columns that will report on SP.TIA.Best Regards,Jim |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-17 : 13:25:20
|
| I can't see that that is possible. The SProc might return several recordsets, any of which might be conditionally output.Kristen |
 |
|
|
dotnetmick
Starting Member
5 Posts |
Posted - 2004-06-17 : 14:50:28
|
| I played around with Visual Studio.NET 2003's server explorer and its editing stuff for stored procedures and I am almost certain that it showed potential output columns. I'm assuming that it did this on its own by examing the stored procedure. I know this probably doesn't help very much...sorry.Mick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-17 : 15:47:36
|
| Something looking at the OUTPUT from the SProc would be able to get Columns names, type and widths from ADO [for example], which would be a start.Doesn't cover possible variable output with conditional recordsets though.Nonetheless it would be a useful tool ...Kristen |
|
|
|
Apollois
Starting Member
49 Posts |
Posted - 2004-06-17 : 17:35:59
|
Kirsten, quote:
I can't see that that is possible.
I don't know how it does it, but an Access 2003 project (with a connectin to the DB) is able to present a list of columns from a SP in the combobox wizard.I agree there is a problem with SPs that can output multiple columnsets, or different columnsets based on criteria. But most SPs that I have output a fixed set of columns.Best Regards,Jim |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-17 : 17:40:18
|
| EXEC sp_sproc_columns 'stored procedure name' Is that what you're looking for?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Apollois
Starting Member
49 Posts |
Posted - 2004-06-17 : 17:53:39
|
Derrick,For a minute there I thought you had hit the jackpot!Unfortunately, in QA it only returns the SP params. From BOL: quote:
In SQL Server, only the column information about input and output parameters for the stored procedure are returned.
Haven't tried it yet using ADO. Perhaps this will give all columns.Hmmm, seems like I remember seeing a way to use ADO from QA. Anybody know how to do this?Best Regards,Jim |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-17 : 23:47:28
|
| As Derrick said, you can get the params with sp_proc_columns. PowerBuilder runs; select name, type, length, colid, prec, scale from dbo.syscolumns where id = OBJECT_ID('dbo.p_myproc') If you don't mind actually running the procedure, you can capture the output into a temp table and get the column names and datatypes from there.PowerBuilder does this and I suspect Visual Studio does to. PB passes '0' for varchar params, 0 for number params, and '7-14-1990' (strange) for dates. If the proc will actually return a result set with bogus paramaters you can run:SELECT TOP 0 * INTO #tmp FROM OPENROWSET ('SQLoledb', 'uid=<username>;pwd=<password>;Network=DBMSSOCN;Address=<servername>,1433;', 'EXEC OPENDATASOURCE(''SQLoledb'', ''Data Source=<Servername>; uid=<username>;pwd=<password>'').db.dbo.p_myproc @pvarchar1 = ''0'', @pvarcahr2 = ''0'', @pint3 = 0')substituting param names with the ones you got from sp_prod_columns. Then you can run sp_proc_columns (i guess on tempdb) or; select name, type, length, colid, prec, scale from tempdb.dbo.syscolumns where id = OBJECT_ID('tempdb..#tmp') There may be an easier way to get output from a stored procedure into a temp table but at least this works. |
|
|
|
|
|
|
|
|
List Columns Output by SP