Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Ukraune, Odessa
    Posts
    4

    Question Unanswered: How dop all local temporary tables

    Hi.

    I want to use local temporary tables (select * into #.. from ..) in my program, but we use connection pool for work with database.
    and i have problem with local temporary tables, that live not during
    transaction, but until connection closed,
    so when i put connection in connection pool the temporary table still live in database.

    question is how get list of local temporary tables for
    some connection for droping them automaticaly??????
    not puting all times by had commands "drop table #...."

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Officially, only theowner of a temporary table (beginning with #) can drop it. Else, the closure of each session will automatically remove the temporary tables.

    Check this sample : the session id is 19, and you can find this id in the name of the temporary table.
    Code:
    1> create table #t (i  int)
    2> create table #t2 (i  int)
    3> go
    1> use tempdb
    2> go
    1> select name from sysobjects where type="U"
    2> go
    
     name
     ------------------------------
     #t___________00000190021942729
     #t2__________00000190021942729
    
    (2 rows affected)
    1> select @@spid
    2> go
    
     ------
         19
    
    (1 row affected)

    ... but an other user can't easily drop these tables

    Code:
    1> select @@spid
    2> go
    
     ------
         23
    
    (1 row affected)
    1> use tempdb
    2> go
    1> drop table #t2__________00000190021942729
    2> go
    Msg 3701, Level 11, State 1:
    Server 'ZEUS', Line 1:
    Cannot drop the table '#t2__________00000190021942729', because it doesn't exist in the system catalogs.
    Last edited by fadace; 01-04-04 at 15:29.

  3. #3
    Join Date
    Jan 2004
    Location
    Ukraune, Odessa
    Posts
    4
    Originally posted by fadace
    Officially, only theowner of a temporary table (beginning with #) can drop it. Else, the closure of each session will automatically remove the temporary tables.

    [/code]
    Yes, I agree with this.
    but when i use conection pool i'm he owner of temp table and i want drop it.
    question is how get list of my temp tables

    can i get list #t1, #t2 .....
    and execute drop #t1, #t2 ????
    not having to drop tables from tempdb ????

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Code:
    SELECT convert(varchar(13),name) TBLName 
    FROM sysobjects 
    WHERE type="U" 
    AND @@spid=convert(int, substring(name, 14, 7))
    Then you must omit the last underlines to find your table names.

  5. #5
    Join Date
    Jan 2004
    Location
    Ukraune, Odessa
    Posts
    4
    Originally posted by fadace
    Code:
    SELECT convert(varchar(13),name) TBLName 
    FROM sysobjects 
    WHERE type="U" 
    AND @@spid=convert(int, substring(name, 14, 7))
    Then you must omit the last underlines to find your table names.
    it's good, but how to be when table name legth more then 12 symbols
    or end with '_' ?????
    those table's names i cannot get by this method.

    example:
    Code:
    select 1 as aaa into #1234567890qwerty
    
    drop table #1234567890qw
    Cannot drop the table '#1234567890qw', because it doesn't exist in the system catalogs. 
    
    drop table #1234567890qwerty 
    ok.

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Try
    Code:
    drop table #1234567890qwe

  7. #7
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    13, excluding the #. Try
    Code:
    drop table #1234567890qwe

  8. #8
    Join Date
    Jan 2004
    Location
    Ukraune, Odessa
    Posts
    4
    Originally posted by fadace
    13, excluding the #. Try
    Code:
    drop table #1234567890qwe
    no, its not work
    only full name needed

Posting Permissions

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