Where are temp tables stored? 3

[Ovatvvon]

Hi, when I run the following script in SQL 2000 Query Analyzer, I try to find the created table in the database, as well as TempDB, however, do not see it listed anywhere. If I keep QA open, I can continue to interact with the table (select, insert, etc), but do not see it listed anywhere.

Does anyone know where it is stored?

USE {db_name}
GO

CREATE TABLE #tmpTable(
	id int PRIMARY KEY,
	txt varchar(25)
)

INSERT INTO #tmpTable (id, txt) VALUES (1, 'Test 1')
INSERT INTO #tmpTable (id, txt) VALUES (2, 'Test 2')
INSERT INTO #tmpTable (id, txt) VALUES (3, 'Test 3')

SELECT * FROM #tmpTable

-Ovatvvon :-Q

 

[SQLDenis]

in tempdb but not with that name it will have a diff name



run this (in the same window)

use tempdb

select * from information_schema.tables
where table_name like'#tmpTable%'

name should be something like #tmpTable______________________(some number atthe end)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

for 2000:

System Database - tempdb - Tables - Temporary Tables

< M!ke >
Acupuncture Development: a jab well done.

 

[LNBruno]

um, yeah...what Denis said

(man you're fast!!!)

< M!ke >
Acupuncture Development: a jab well done.

 

[ca8msm]

It gets created in the tempdb database. In SQL Server Management Studio (SQL Server 2005) there is an item called Temporary Tables although I can't remember if the same applies to 2000.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Ovatvvon]

Thank you all for your responses!

SQL 2000 (Enterprise Manager) does not list Temporary Tables in a separate folder like in Management Studio (SQL 2005).

I do not see anything listed in E.M. When I use Management Studio, I do see a bunch of temp tables listed, however, none of them have alpha-characters in the name, only a pound sign and a bunch of numbers. (None list "tmpTable" as part of the name).

As a result, when using the sql statement:

select * from information_schema.tables
where table_name like'#tmpTable%'

or

select * from information_schema.tables
where table_name like'%tmpTable%'

...it returns nothing.

If I run the following statement afterwards: Drop Table #tmpTable, it succeeds without error.

Any ideas?


-Ovatvvon :-Q

 

[SQLDenis]

yes are you in the tempdb when running that statement?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Ovatvvon]

Yep. Specified TempDB, and also tried various others - just for kicks.


-Ovatvvon :-Q

 

[Ovatvvon]

Partially disregard my last...

I was connected to the wrong server for Management Studio (as much as I hate to have to admit that)! Hundreds of servers here...and a temporary brain laps...

Anyway, when using Management Studio, I now see a Temporary Table in TempDB called "#tmpTable____________...", however, still nothing in Enterprise Manager, and Query Analyzer doesn't return anything when running the script:

USE TempDB
GO
select * from information_schema.tables
where table_name like'%tmpTable%'

I guess that does answer my question though. I had thought it was in TempDB, but was not using Management Studio, and so couldn't find it anywhere.

Now I know with certainty.

Thanks!


-Ovatvvon :-Q