0

A few efficient methods were shown in this post and copied below but that doesn't work for temporary tables. I really want to avoiding COUNT(*) due to the table size.

The first one below returns and error and the second NULL

SELECT CONVERT(bigint, rows) 
FROM sysindexes 
WHERE id = OBJECT_ID(#temporaryTable) 
AND indid < 2 

SELECT SUM (row_count) 
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID(#temporaryTable)    
AND (index_id=0 or index_id=1);
GMB
  • 216,147
  • 25
  • 84
  • 135
XavU
  • 3
  • 1
  • 1
    The second one should work as long as you qualify the DB in all cases -- `select ... from tempdb.sys.dm_db_partition_stats where object_id = OBJECT_ID('tempdb.dbo.#temporaryTable')` (disclaimer: untested, I have no live instance atm). – Jeroen Mostert Aug 17 '20 at 21:26
  • It still return NULL when fully qualified and using a SELECT * – XavU Aug 17 '20 at 21:34
  • @XavU. I bet you're running Jeroen's code in another tab. I think `object_id()` won't properly identify #temporaryTable in that case. – pwilcox Aug 17 '20 at 21:45

3 Answers3

0

If your main goal is to find the total number of rows, you can perhaps run this:

SELECT MAX(id) FROM sys.dm_db_partition_stats

This returns the last id that was logged and thus will be the number of rows (given that id is incrementing serially).

pmoh
  • 81
  • 8
0

It's because you've got to query tempdb, not whatever current database you're in. I filtered the specific table by joining to sysobjects. When doing that, you have to know that #temporaryTable is actually not really named that, but just starts with that name.

select      o.name,
            rows = CONVERT(bigint, rows) 
from        tempdb..sysindexes i
join        tempdb..sysobjects o on i.id = o.id
where       o.name like '#temporaryTable_%' 
and         indid < 2 

select      o.name,
            row_count = sum(ps.row_count)
from        tempdb.sys.dm_db_partition_stats ps
join        tempdb..sysobjects o on ps.object_id = o.id
where       o.name like '#temporaryTable_%' 
and         (index_id=0 or index_id=1)
group by    o.name
pwilcox
  • 5,542
  • 1
  • 19
  • 31
  • 1
    Different sessions can have their own `#temporaryTable` instances, and partially matching on name means you can get multiple results with no way to know which is which. Using `OBJECT_ID('tempdb.dbo.#temporaryTable')` will only work in the session that created the table, but with the benefit that it will actually get the table created in that session. Which of these approaches is to be preferred depends on what you're after, of course, which the question doesn't make clear. – Jeroen Mostert Aug 17 '20 at 22:16
  • Thanks Jeroen, that's good to know. I'll make changes to make sure the table works in that session. – XavU Aug 20 '20 at 00:16
0

If the total number of rows is your primary goal

SELECT IDENTITY(INT,1,1) AS ID, FirstName,Surname  INTO NewTable FROM ExistingTable;

select max(ID) from NewTable;

Note: When another column inherits the identity property, this does not work.

tdy
  • 36,675
  • 19
  • 86
  • 83
Neeshu Chaudhary
  • 1
  • 1