what are #0E391C95 like tables in tempdb

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

what are #0E391C95 like tables in tempdb

Author

Topic

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-04-15 : 01:47:15

Hi,

When i am trying to see all temporary tables from tempdb by executing following command

select * from tempdb.sys.tables

I found '#0E391C95' like tables .What are these tables and i am not able to drop these,why?

Please help me...

Thanks.

sathish

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-04-15 : 06:04:24

This tables are related to table variables

I came know it by following Experiment

when i open a new connection and executed

select * from tempdb.sys.tables

i get null results.

declare @a table (sal int)
insert into @a values(1)
waitfor delay '00:01:15'
select * from @a

while execution of this code i executed following code from one more user
select * from tempdb.sys.tables

i found one table '#1B9317B3' after the completion of execution of the code this table deleted automatically.

so i conclude that it is related to table variables.

sathish

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-15 : 06:17:45

They are either table variables or cached temp tables.

With any form of temp object, only the connection that created it can drop it.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources