Solved

SQL Server - get info on columns returned in recordset

Posted on 2011-03-07
7
729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 40

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 40

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
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)

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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