We help IT Professionals succeed at work.

query size/count of temp table

websss
websss asked
on
I have a run longing script (30 days) and I want to see the progress/size of one of the temp tables

however this doesn't work
select count(*) from [tempdb].[dbo].[#DataToDeleteTable]

Open in new window


its says
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 8
Invalid object name '#DataToDeleteTable'.

Open in new window


I've also tried
SELECT COUNT(*) 
FROM #DataToDeleteTable

Open in new window


but get
Msg 208, Level 16, State 0, Line 5
Invalid object name '#DataToDeleteTable'.

Open in new window


if i run this
select * from tempdb.sys.objects

Open in new window


I see the name
#DataToDeleteTable__________________________________________________________________________________________________000000005144

Open in new window


How might I get the count of this temp table?
Comment
Watch Question

View Solution Only

Commented:
Temporary tables with a single octothorpe #table is designed to be isolated and visible in a session only.

Temporary tables with two octothorpes ##table are designed to be visible globally.

However, try this:

SELECT
    T.NAME AS TABLE_NAME,
    S.ROW_COUNT
FROM TEMPDB.sys.dm_db_partition_stats AS S 
INNER JOIN TEMPDB.sys.tables AS T 
ON S.OBJECT_ID = T.OBJECT_ID 
WHERE S.INDEX_ID < 2
and T.NAME like '%#DataToDeleteTable%';

Open in new window

websssCEO

Author

Commented:
perfect thanks

76,285,392 rows left