September 1, 2009 at 3:52 am
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
🙂
September 1, 2009 at 3:55 am
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 ?
September 1, 2009 at 4:06 am
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