Prev: Convert datetime
Next: CASE Statement when ranges involved
From: Terry Holland on 18 Jul 2008 07:24
Is it possible to get metadata about columns returned by a strored procedure
in much the same way as you would from syscolumns?

the info im interested in is

Ordinal
TypeID
MaxLength
Precision
Scale
Nullable
IsIdentity

tia

From: Dan Guzman on 18 Jul 2008 08:06
I don't believe stored procedure result set meta data is persisted. One way
to get result set info is by executing the proc with SET FMTONLY ON through
a client API and iterating over the result set columns. Another method is
to insert the result into a table using OPENROWSET and then querying the
resultant table meta data.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Terry Holland" <MSDNNospam248(a)nospam.nospam> wrote in message
news:DBD4928C-46B6-4B63-BC92-1893F4D883B3(a)microsoft.com...
> Is it possible to get metadata about columns returned by a strored
> procedure
> in much the same way as you would from syscolumns?
>
> the info im interested in is
>
> Ordinal
> TypeID
> MaxLength
> Precision
> Scale
> Nullable
> IsIdentity
>
> tia
>

From: Terry Holland on 18 Jul 2008 09:42
thanks

"Dan Guzman" wrote:

> I don't believe stored procedure result set meta data is persisted. One way
> to get result set info is by executing the proc with SET FMTONLY ON through
> a client API and iterating over the result set columns. Another method is
> to insert the result into a table using OPENROWSET and then querying the
> resultant table meta data.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Terry Holland" <MSDNNospam248(a)nospam.nospam> wrote in message
> news:DBD4928C-46B6-4B63-BC92-1893F4D883B3(a)microsoft.com...
> > Is it possible to get metadata about columns returned by a strored
> > procedure
> > in much the same way as you would from syscolumns?
> >
> > the info im interested in is
> >
> > Ordinal
> > TypeID
> > MaxLength
> > Precision
> > Scale
> > Nullable
> > IsIdentity
> >
> > tia
> >
>
From: Alex Kuznetsov on 18 Jul 2008 09:57
On Jul 18, 8:42 am, Terry Holland <MSDNNospam...(a)nospam.nospam> wrote:
> thanks
>
> "Dan Guzman" wrote:
> > I don't believe stored procedure result set meta data is persisted. One way
> > to get result set info is by executing the proc with SET FMTONLY ON through
> > a client API and iterating over the result set columns. Another method is
> > to insert the result into a table using OPENROWSET and then querying the
> > resultant table meta data.
>
> > --
> > Hope this helps.
>
> > Dan Guzman
> > SQL Server MVP
> >http://weblogs.sqlteam.com/dang/
>
> > "Terry Holland" <MSDNNospam...(a)nospam.nospam> wrote in message
> >news:DBD4928C-46B6-4B63-BC92-1893F4D883B3(a)microsoft.com...
> > > Is it possible to get metadata about columns returned by a strored
> > > procedure
> > > in much the same way as you would from syscolumns?
>
> > > the info im interested in is
>
> > > Ordinal
> > > TypeID
> > > MaxLength
> > > Precision
> > > Scale
> > > Nullable
> > > IsIdentity
>
> > > tia

Note that a stored procedure may return several result sets, or
different ones depending on some logic. So there are no reliable ways
to accomplish what you are asking.
 | 
Pages: 1
Prev: Convert datetime
Next: CASE Statement when ranges involved