November 14, 2013 at 8:01 am
Hello:
For a permanent table using ''exec sp_spaceused <tablename> " will show me how much space is used by a table .... or can query sys.sysindexes, etc. to find out how much space a set of data takes up.
Is there a way to find out how much space a temporary table uses?
For example, if a query is used to
select col1, col2, col3, etc.
into #tmpTableX
from tablea a join tableb b on a.col5= b.col4
left outer join tablec , etc.
sp_spaceused does not work on a temporary table, neither does the heap index get recorded to sysindexes
This was something that we wanted to use to do some quick estimations on how to size an initial database that will be created and populated by using a series of queries to pull data from another database.
Thought it would be quicker than working out the exact data types/sizes of each column pulled in the queries and calculating the row size [to mulitply by the anticipated record count].
Thank you for your insight.
November 14, 2013 at 8:09 am
Okay ... just ignore me.
I forgot to use sp_spaceused to look for the temp table in the tempdb database !
which is exactly where it was.
The "error message" had said :
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object '#tmp_STATUS_HX' does not exist in database My_User_Database' or is invalid for this operation.
Sorry, I jumped to the conclusion that a temp table was invalid for the operation.
But, this post may server as a reminder for others.
You can indeed use sp_spaceused against a temp table ... as long as you are in the tempdb database [or fully qualify the name]
Thank you.
November 14, 2013 at 8:09 am
if you switch your database context to tempdb, sp_spaceused works for me:
select top 100 * into #temp from edlogdetail
use tempdb
exec sp_spaceused #temp
/*
namerowsreserveddataindex_sizeunused
#temp_<snip>_000000000187100 56 KB48 KB8 KB0 KB
*/
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply