Post reply

Get Temp Table definition

  • April 13, 2018 at 4:26 pm

    #403263

    Comments posted to this topic are about the item Get Temp Table definition

    Wes
    (A solid design is always preferable to a creative workaround)

  • April 19, 2018 at 1:13 am

    #1987454

    I like this - thank you 😀

  • April 19, 2018 at 7:50 am

    #1987521

    Thanks very much, very useful.

  • April 19, 2018 at 10:07 am

    #1987560

    Apparently with some data types or some SQL versions, some columns may be duplicated with a sysname datatype.  I've submitted an update to the original script, but it may take a few days for the update to be approved.

    In the Results CTE

     FROM     tempdb.sys.columns AS C
      INNER JOIN   tempdb.sys.types AS T2
       ON C.system_type_id = T2.system_type_id 

    change the ON clause above to this

    ON c.user_type_id = t2.user_type_id

    Wes
    (A solid design is always preferable to a creative workaround)

  • August 15, 2018 at 7:59 am

    #2001872

    You can
    exec tempdb.dbo.sp_help '#temp'
    and you have all details about #temp

    Igor Micev,My blog: www.igormicev.com

  • August 15, 2018 at 8:16 am

    #2001884

    Igor Micev - Wednesday, August 15, 2018 7:59 AM

    You can
    exec tempdb.dbo.sp_help '#temp'
    and you have all details about #temp

    Agreed.  And it's excellent for designing and troubleshooting.  It is, however, a bit difficult to automate if you're dealing with unknown inputs that are present in the Temp Table because you used SELECT/INTO to create the Temp Table and you need your code to be able to read the results so that it may make decisions about the data based on meta-data in the Temp Table. 

    As a bit of a sidebar, I think that its a shame that MS doesn't have a separate extended procedure that can actually be used to return an easily consumable meta-data table about any table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 20, 2018 at 9:13 am

    #2016968

    I really like this script. Just been using it to create definitions or temporary tables that have been created using INTO #... which later had a problem with NULL columns when I tried to create a primary key on them. So it allows me to change the definition to be NOT NULL.

  • December 20, 2018 at 1:06 pm

    #2016983

    Jonathan AC Roberts - Thursday, December 20, 2018 9:13 AM

    I really like this script. Just been using it to create definitions or temporary tables that have been created using INTO #... which later had a problem with NULL columns when I tried to create a primary key on them. So it allows me to change the definition to be NOT NULL.

    If you want to create NOT NULL columns in a table created by SELECT/INTO, just encapsulate the items in the SELECT list with a viable ISNULL function even if its impossible to have a NULL in the column.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 20, 2018 at 1:15 pm

    #2016984

    Jeff Moden - Thursday, December 20, 2018 1:06 PM

    Jonathan AC Roberts - Thursday, December 20, 2018 9:13 AM

    I really like this script. Just been using it to create definitions or temporary tables that have been created using INTO #... which later had a problem with NULL columns when I tried to create a primary key on them. So it allows me to change the definition to be NOT NULL.

    If you want to create NOT NULL columns in a table created by SELECT/INTO, just encapsulate the items in the SELECT list with a viable ISNULL function even if its impossible to have a NULL in the column.

    Thanks, sounds a better way of doing it.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply