Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

SQL Server - get info on columns returned in recordset

I'd like to know if there is an sp_ system stored procedure that will return info on the Columns in a recordset (if the stored procedure returns a recordset).

Thanks!

I'm using SQL Server 2008 R2.
0
hindersaliva
Asked:
hindersaliva
  • 3
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
This should do it in SQL 2008 - the SP is in Master DB:

USE [master]
GO
/****** Object:  StoredProcedure [sys].[sp_sproc_columns_100]    Script Date: 03/07/2011 13:38:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [sys].[sp_sproc_columns_100]
(
    @procedure_name         nvarchar(390) = '%',
    @procedure_owner        nvarchar(384) = null,
    @procedure_qualifier    sysname = null,
    @column_name            nvarchar(384) = null,
    @ODBCVer                int = 2,
    @fUsePattern            bit = 1 -- To allow users to explicitly disable all pattern matching.
).....................
0
 
hindersalivaAuthor Commented:
Icohan, I get this error

Incorrect syntax near '.'.
0
 
lcohanDatabase AnalystCommented:
You asked if there is a "SP_..." in SQL 2008 that can give you the column info and I showed you which one is it but did you executed the SP mentioned above passing the parameters when you got the error?

You must run something like below in SQL to get the column info for a sp that returns a recordset:

EXEC [sys].[sp_sproc_columns_100]
    @procedure_name         = 'your_user_sp_name_here',
    @procedure_owner        = null,
    @procedure_qualifier    = null,
    @column_name            = null,
    @ODBCVer                = 2,
    @fUsePattern            = 1 -- To allow users to explicitly disable all pattern matching
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
hindersalivaAuthor Commented:
Icohan, thank you for helping.

I got the SP to run - but it returns a list of the Parameters the SP expects, rather than the Columns it would return in a Recordset :(

0
 
Anthony PerkinsCommented:
For that you need to use:
SET FMTONLY ON;
0
 
Anthony PerkinsCommented:
Here is a good link on the subject:
SET FMTONLY (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173839.aspx
0
 
hindersalivaAuthor Commented:
acperkins,
That's perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now