Find Temp Tables From SPID

• shantaram-815439

  SSC Eights!

  Points: 814

  More actions

  September 1, 2009 at 3:52 am

  #214477

  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

  🙂

• Dave Ballantyne

  SSC-Dedicated

  Points: 33556

  More actions

  September 1, 2009 at 3:55 am

  #1047381

  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]

• shantaram-815439

  SSC Eights!

  Points: 814

  More actions

  September 1, 2009 at 4:06 am

  #1047385

  Yes, I know that.

  But I have some requirement for the same.

  Pls..