Solved

SQL Server - get info on columns returned in recordset

Posted on 2011-03-07
7
715 Views
Last Modified: 2012-05-11
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
Comment
Question by:hindersaliva
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 35059649
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
 

Author Comment

by:hindersaliva
ID: 35196832
Icohan, I get this error

Incorrect syntax near '.'.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35201027
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:hindersaliva
ID: 35203297
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35203579
For that you need to use:
SET FMTONLY ON;
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35203583
Here is a good link on the subject:
SET FMTONLY (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173839.aspx
0
 

Author Comment

by:hindersaliva
ID: 35203678
acperkins,
That's perfect!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now