Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: information_schema for temp table ?

    Hi there!

    I'm trying to find how can I get the information_schema for a temp table.

    I'm trying to find all columns of a temp table.

    So it will be something like this SELECT * FROM information_schema.columns

    But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns .... nada....


    Please help!


    Thanks,

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i don't know what you are really trying to do but try...

    SELECT * FROM #MyTempTable WHERE 1 = 0 will give the column names.

    I know I do not want know the answer but why do you not know the structure of the temp table?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I don't know the structure of my temp table because I use pivot tables.

    And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.

    So it's a real pain in the a**.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by ortho
    I don't know the structure of my temp table because I use pivot tables.
    I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.

    Quote Originally Posted by ortho
    And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.
    it aint on the resume no more but i did some VB6 once upon a time. how are are you doing the export? There are a few ways to do this. Recently there was a thread here and there is some info on sqlteam about how to BCP out column names. Have you thought about using BCP?

    However I am guessing you are doing the old Open #1 FOR OUTPUT or whatever it was or perhaps you are using filesystem objects.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    No I use CopyFromRecordSet of the Excel.Application.

    What's BCP by the way ?

    I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.
    As I use a date as a pivot the number of columns still increase day by day
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    bulk copy program. google it. or filesystem objects. heck google SQL Server DTS. Or "Visual Basic 6 Open file". I even bet if you check your VBA documentaion, you will find a way to do this. There are many ways to skin a cat.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I did google the bulk copy, which is very intresting feature of sql.

    I also checked at the vba documentions and no body mention how to do that or if it's doable... anyways ...

    I'll try to find a way...

    Thanks
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I found the best way to do it by myself, it was so easy lol... shame on me.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    enlighten us.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    As i said, I use it in a VB6 app that I made, and the results of the stored proc is stored in a record set so I build an array like this

    array(#)= rs.fields(#).name....

    It was SOOOO simple...

    anyway... thank you all
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •