I am using SQL Server 2000 and I want to be able to list out all the field
names and their datatypes from a particular table.

I found out that sp_help does that but unfortunately when using a recordset,
I could only get the first of the 3 tables.

Is there anyway to get the second table using a recordset because that is
the one with all the info about the fields and their datatypes?

Or is there another way of finding out the datatypes of the fields?

You move to the next 'Table' by using the "NextRecordset" method.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Gervin" <gervin@pd.jaring.nospam.my> wrote in message news:bvqrff$cgt$1@news4.jaring.my...[color=blue]
> I am using SQL Server 2000 and I want to be able to list out all the field
> names and their datatypes from a particular table.
>
> I found out that sp_help does that but unfortunately when using a recordset,
> I could only get the first of the 3 tables.
>
> Is there anyway to get the second table using a recordset because that is
> the one with all the info about the fields and their datatypes?
>
> Or is there another way of finding out the datatypes of the fields?
>
>[/color]

set rs = conn.execute("EXEC sp_help 'tablename'")

do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop

set rs = rs.nextRecordSet()
do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop

set rs = rs.nextRecordSet()
do while not rs.eof
response.write rs(0) & "<br>"
rs.movenext
loop


Also see the first example in http://www.aspfaq.com/2177 for a more concise
and exhaustive output, that doesn't require multiple resultsets.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"Gervin" <gervin@pd.jaring.nospam.my> wrote in message
news:bvqrff$cgt$1@news4.jaring.my...[color=blue]
> I am using SQL Server 2000 and I want to be able to list out all the field
> names and their datatypes from a particular table.
>
> I found out that sp_help does that but unfortunately when using a[/color]
recordset,[color=blue]
> I could only get the first of the 3 tables.
>
> Is there anyway to get the second table using a recordset because that is
> the one with all the info about the fields and their datatypes?
>
> Or is there another way of finding out the datatypes of the fields?
>
>[/color]

Thanks. I'll give it a try.

"Al Reid" <areidjr@reidDASHhome.com> wrote in message
news:%23%23eRmoy6DHA.488@TK2MSFTNGP12.phx.gbl...[color=blue]
> You move to the next 'Table' by using the "NextRecordset" method.
>
> --
> Al Reid
>
> "It ain't what you don't know that gets you into trouble. It's what you[/color]
know[color=blue]
> for sure that just ain't so." --- Mark Twain
>
> "Gervin" <gervin@pd.jaring.nospam.my> wrote in message[/color]
news:bvqrff$cgt$1@news4.jaring.my...[color=blue][color=green]
> > I am using SQL Server 2000 and I want to be able to list out all the[/color][/color]
field[color=blue][color=green]
> > names and their datatypes from a particular table.
> >
> > I found out that sp_help does that but unfortunately when using a[/color][/color]
recordset,[color=blue][color=green]
> > I could only get the first of the 3 tables.
> >
> > Is there anyway to get the second table using a recordset because that[/color][/color]
is[color=blue][color=green]
> > the one with all the info about the fields and their datatypes?
> >
> > Or is there another way of finding out the datatypes of the fields?
> >
> >[/color]
>
>[/color]

Thanks. I'll give it a try.