Solved

SQL Server - get info on columns returned in recordset

Posted on 2011-03-07
7
735 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 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