Determine field names of stored proc return
-
I'd like to determine the structure (Fields, Data types, etc) of the results of a stored procedure without running the stored procedure.
For example:
Given:
CREATE PROC usp_TestProc1
AS
SELECT CustomerName, CustomerID, CustomerType
FROM tCustomerTable
WHERE CustomerGroup = 'ABC'
RETURN 0;
I would like to have another proc (or a function in VB.net) that could return a data set that would tell me the field names and data types - the results would look something like:
FieldName DataType
CustomerName varchar(35)
CustomerID int
CustomerType char(3)
Even if I had to do this by somehow enumerating the fields collection in VB.net that would work too. If I run the proc and just look at the resulting dataset it takes a lot longer.
Somehow Visual Studio does this because it does not run the procs (it doesn't take enough time to) before it returns a list of field names.
Thanks for any help!
-Jason
-
in TSQL, it's simply setting the SET FMTONLY ON/OFF setting; it returns the datatable with no rows, so it can them be displayed with whatever the column definitions are;
CREATE PROC usp_TestProc2
AS
SELECT name,id,xtype from sysobjects where xtype='U'
RETURN 0;
SET FMTONLY On
exec usp_TestProc2
SET FMTONLY off
results:
name id xtype
---- -- -----
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
What if procedure returns multiple recordsets? Which one you wanna see?
What if procedure does not return any recordset?
What if only result it returns is an output parameter?
_____________
Code for TallyGenerator -
For this application I know that the procedures will only (and always) return one recordset. I'm still testing it right now but the FMTONLY option seems to be working.
-
I only see the column names being returned... no data types...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
when run in QA or anything like that, yes only column names appear...the rest is hidden.
when placed in a client side data table or ADODB.Recordset. the attributes like data type, size, nullable or not are exposed and accessible as part of the data returned fromt he query.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
What do you mean by "client side data table", Lowell?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
it's not blindingly obvious, but in QA when you do a SELECT INTO statement, to dynamically make a new row, the table that gets created from the data is created complete with the data type, right? but QA just displays the values, and not the field attributes.
when inserted into something that is data aware, those properties are a bit easier to see.
here's a screenshot from dotnet as an example:
As you can see, the data type, default, identity, literally everything about the column because availalble to the programming language. these properties are hidden when viewed in QA, but was availallbe a tthe programming level. in QA for example, they could color code cells based on data type if they wanted to, just by using these properties.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That's exactly what I did - I set FMTONLY ON, then run my stored proc, then I turn FMTONLY OFF - I included my VB.net code below. Thanks for the help in this forum!
-Jason
Public Sub loadFields()
Try
Dim con As SqlConnection = DBConn.DB
Dim ds As DataSet = New DataSet
Dim cmd As SqlCommand = New SqlCommand
Dim adp As SqlDataAdapter = New SqlDataAdapter
Dim col As DataColumn
If oFlds Is Nothing Then oFlds = New RACDataSet.RACDSFields
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = "SET FMTONLY ON"
.ExecuteNonQuery()
.CommandType = CommandType.StoredProcedure
.CommandText = Me.DataSource
End With
adp.SelectCommand = cmd
adp.Fill(ds, "Table")
With cmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = "SET FMTONLY OFF"
.ExecuteNonQuery()
End With
oFlds.Clear()
For Each col In ds.Tables(0).Columns
oFlds.Add(New RACDataSet.RACDSField(col, Me, con))
'HERE IS WHERE YOU CAN ACCESS THE DataType
'col.DataType --> returns the column's Data Type
Next
Catch ex As Exception
MsgBox("There was an error retrieving the field information for the selected dataset. Error: " & ex.Message, MsgBoxStyle.Critical, "RACDataSet.loadFields()")
End Try
End Sub
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply