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
Solved

SQL Server - get info on columns returned in recordset

Posted on 2011-03-07
7
728 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Alter a column in sql 34 31
Whats wrong in this query - Select * from tableA,tableA 11 45
union query column need default text 2 15
Are triggers slow? 7 12
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

829 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