How to get the second table from sp_help result?

[Gervin]

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?

[Al Reid]

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" <ge****@pd.jaring.nospam.my> wrote in message news:bv**********@news4.jaring.my...

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?

[Aaron Bertrand - MVP]

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" <ge****@pd.jaring.nospam.my> wrote in message
news:bv**********@news4.jaring.my...

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?

[Gervin]

Thanks. I'll give it a try.

"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2*****************@TK2MSFTNGP12.phx.gbl...

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" <ge****@pd.jaring.nospam.my> wrote in message

news:bv**********@news4.jaring.my...

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?

[Gervin]

Thanks. I'll give it a try.