Find Temp Tables From SPID

  • Hi,

    😛

    I want to know the query to get all the temporary local tables created by any SPID

    Eg. My current SPID is 345

    Then I want a list of all the temporary #tables created by this SPID.

    This requirement is because i am creating many temp tables in my stored proc and have to drop them writing many statements.

    Pls give the solution for SQL server 2008, 2005 as well as 2000

    :hehe:

    Previously I used to write folowing code

    ----------------------

    USE MYDB

    create table #tmp_rajiv

    (id int)

    INSERT INTO #tmp_rajiv VALUES(1)

    SELECT name FROM TEMPDB.DBO.SYSINDEXES WHERE name like '#tmp_rajiv%'

    /*

    OUTPUT=#tmp_rajiv

    */

    SELECT name FROM TEMPDB.DBO.SYSOBJECTS WHERE name like '#tmp_rajiv%'

    /*

    OUTPUT=#tmp_rajiv__________________________________________________________________________________________________________000000006480

    */

    ----------------------

    Thanks in anticipation

    Regards,

    Shantaram

    🙂

  • shantaram (9/1/2009)have to drop them writing many statements

    You dont have to , once they have gone out of scope they will automatically be dropped.

    Knowing that do you still need the query ?



    Clear Sky SQL
    My Blog[/url]

  • Yes, I know that.

    But I have some requirement for the same.

    Pls..

Viewing 3 posts - 1 through 2 (of 2 total)

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