Recently I notice a strange behavior about TEMPDB catalog view, SYSOBJECTS or INFORMATION_SCHEMA.TABLES. For illustration please use the attached code.
1 table script and 2 stored procedures.
Pr_Test2 is calling Pr_Test1 3 times using while loop
Pr_Test1 creates local temp table from regular database table, and then drops the local temp table
Even after Explicitly performing DROP on local temp table, if we query any of the TEMPDB catalog view, TEMPDB.SYS.SYSOBJECTS or TEMPDB. INFORMATION_SCHEMA.TABLES they still have an entry for the same table.
Though actual table is actually dropped and user will not be able to access that after drop statement but in situations where query logic is inquiring about “what were the columns on that local temp table it will be still accessible”.