Monday, June 16, 2003

I think that the temporary tables get dropped as soon as the stored procedure finishes execution. You could try a simple test in Query Analyzer: - Open a connection - Execute the following: drop proc foo go create proc foo as create table #foo (col1 int) insert #foo values (1) select * from #foo --waitfor delay '1:00:00' go exec foo go select * from #foo go drop table #foo go select count(*) from tempdb..sysobjects where name like '#foo%' go You will notice that once the procedure execution is over, the temporary table cannot be referred even from the same connection. - Now, uncomment the "waitfor", drop & create & execute the proc.  From another connection, execute the following : select * from #foo go drop table #foo go select count(*) from tempdb..sysobjects where name like '#foo%' go You will notice that there is a table like "#foo", but it cannot be referenced by anyone else. Also, once you cancel the execution of the proc, then that table cannot be noticed in the "sysobjects" system table. The temporary tables should get destroyed after stored procedure execution are completed, you do not need to "DROP TABLE #foo" at the end of the proc. You could execute DROP table if you wish to, but it is not necessary.   If you still wish to 'monitor' the temporary tables, you could query the "tempdb..sysobjects" system table. 12:25:55 PM    trackback []     Articulate []

The syntax to test if a temporary table already exists or not? create table #test ( mycol int ) if object_id( 'tempdb..#test' ) is not null print 'exists' 12:00:39 PM    trackback []     Articulate []