Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: sybase temp table names

    I'm struggling to write a query to produce the names of the temp table that are created in my database.

    I can name a temp table with, let's say, 20 characters, #my_sybase_temp_table. When I query on the tempdb..sysobjects table(type='U'), the name appears to be truncated to roughly 12 positions, and then a set of numbers is concatinated to the end.

    Does anyone out there have the "magical" query that will return the full table name?

    Thanks,
    Scott

  2. #2
    Join Date
    Jun 2003
    Posts
    140

    Re: sybase temp table names

    Hi there,

    there doesnt seem to be any apparent way to do so as Sybase assigns
    active spid and other internal codes to temp table name , if you want to just access it and dont care abt table name u can refer to object_id column of sysobjects









    Originally posted by ScottW
    I'm struggling to write a query to produce the names of the temp table that are created in my database.

    I can name a temp table with, let's say, 20 characters, #my_sybase_temp_table. When I query on the tempdb..sysobjects table(type='U'), the name appears to be truncated to roughly 12 positions, and then a set of numbers is concatinated to the end.

    Does anyone out there have the "magical" query that will return the full table name?

    Thanks,
    Scott

  3. #3
    Join Date
    Oct 2003
    Posts
    18

    Re: sybase temp table names

    Temp tables disappear after the batch is finished and isn't meant to be accessed "from the outside". In fact, I think it's impossible.

    Normal tables in tempdb is another matter. If you need to share data between your batches, create a normal table in tempdb and access it like any other table, only with tempdb.. prepended to the name.

  4. #4
    Join Date
    Jan 2003
    Posts
    62

    Re: sybase temp table names

    >I can name a temp table with, let's say, 20 characters, >#my_sybase_temp_table. When I query on the tempdb..sysobjects >table(type='U'), the name appears to be truncated to roughly 12 >positions, and then a set of numbers is concatinated to the end.

    The max limit for name is 30 chars. For temp table, some internal code is generated for the name in sysobjects. So, you may end up with different name depending on the length of the orginal name you've input:
    e.g.
    a) create table #1234567890 (c1 int)

    #1234567890__00000150010378724

    b) create table #12345678901234567890 (c1 int)

    #12345678901200000150010378724


    As far I know, only the first 13 char (including '#') is allocated for the name and any thing more than that is truncated. If you enter a name that is less than that, the '_' char is used to fill up the remaining.
    The next 2 chars represents something I not too sure.
    The next 5 chars contains the spid as seen in sp_who.
    The remaining, I guess is some time stamp.

  5. #5
    Join Date
    Oct 2003
    Posts
    10

    Thanks!

    Thanks for the responses. This confirms what I was finding.

Posting Permissions

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