none
I cannot find the table that was created? RRS feed

  • Question

  • hi friend,

    i have create a table with the "#" symbol as

    create table @clients (col1,  col1)

    it was successfully created, but i cannot see it under the table collection, why is that?

    when i prefix a table with "#" symbol, does that become a special table?

    thanks

     

    Wednesday, June 23, 2010 1:37 AM

Answers

  • Do you mean # or @?

    If you created the table with a name that starts with #, it becomes a temporary table and is created in the tempdb database. You can only access the table in the connection in which is was created, and when the connection is closed, the table is dropped. Please read about temporary tables in the documentation.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Proposed as answer by Naomi NModerator Wednesday, June 23, 2010 1:56 AM
    • Marked as answer by KJian_ Tuesday, June 29, 2010 5:59 AM
    Wednesday, June 23, 2010 1:44 AM
    Moderator
  • Yes in the same connection. In other words, you can create temp table in the main SP and it would be visible in the SPs that are called from that SP.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, June 29, 2010 5:59 AM
    Wednesday, June 23, 2010 2:30 AM
    Moderator

All replies

  • Do you mean # or @?

    If you created the table with a name that starts with #, it becomes a temporary table and is created in the tempdb database. You can only access the table in the connection in which is was created, and when the connection is closed, the table is dropped. Please read about temporary tables in the documentation.


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Proposed as answer by Naomi NModerator Wednesday, June 23, 2010 1:56 AM
    • Marked as answer by KJian_ Tuesday, June 29, 2010 5:59 AM
    Wednesday, June 23, 2010 1:44 AM
    Moderator
  • Do you mean # or @?

    If you created the table with a name that starts with #, it becomes a temporary table and is created in the tempdb database. You can only access the table in the connection in which is was created, and when the connection is closed, the table is dropped. Please read about temporary tables in the documentation.


    HTH, Kalen Delaney www.SQLServerInternals.com


    so when i create the temp table, could it be accessed by stored procedures in other dbs?

    thanks

    Wednesday, June 23, 2010 2:25 AM
  • Yes in the same connection. In other words, you can create temp table in the main SP and it would be visible in the SPs that are called from that SP.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Tuesday, June 29, 2010 5:59 AM
    Wednesday, June 23, 2010 2:30 AM
    Moderator