How to get the second table from sp_help result?

Discussion in 'ASP General' started by Gervin, Feb 4, 2004.

  1. Gervin

    Gervin Guest

    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, Feb 4, 2004
    #1
    1. Advertising

  2. Gervin

    Al Reid Guest

    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" <> wrote in message news:bvqrff$cgt$...
    > 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, Feb 4, 2004
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:bvqrff$cgt$...
    > 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, Feb 4, 2004
    #3
  4. Gervin

    Gervin Guest

    Thanks. I'll give it a try.

    "Al Reid" <> wrote in message
    news:%23%...
    > 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" <> wrote in message

    news:bvqrff$cgt$...
    > > 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, Feb 5, 2004
    #4
  5. Gervin

    Gervin Guest

    Thanks. I'll give it a try.
    Gervin, Feb 5, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. J.Ram
    Replies:
    7
    Views:
    640
  2. Pavel
    Replies:
    7
    Views:
    512
    Pavel
    Sep 19, 2010
  3. Lakshmi Sreekanth

    i = 10; result = ++i - --i; How result become ZERO

    Lakshmi Sreekanth, Sep 21, 2010, in forum: C Programming
    Replies:
    52
    Views:
    1,147
    Nick Keighley
    Sep 23, 2010
  4. Michael Tan
    Replies:
    32
    Views:
    937
    Ara.T.Howard
    Jul 21, 2005
  5. yelipolok
    Replies:
    4
    Views:
    242
    John W. Krahn
    Jan 27, 2010
Loading...

Share This Page