How to Find Table size?

Question

Post reply

How to Find Table size?

vaasan.sundararajan

Grasshopper

Points: 22
More actions
September 14, 2010 at 11:13 pm

#226883

I want to get the table size in KB. I will give the following inputs, Table Name and Number of Rows. Based on my input query should display the size in kilo bytes. If the table contains any index that should also be included.
will it possible?
Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This is a great script I got from Jeff Moden that should do the trick for you.

Hope this helps

Gianluca

--===== "Space Used on Steroids"

-- If "UnusedKB" is negative, it's likely you need to run DBCC UpdateUsage on the table.

-- If the RowModCtr is high (contains number of rows inserted/updated/deleted sinse last stats update)

-- you might want to run UPDATE STATISICS on those tables.

-- Jeff Moden

;WITH SpaceUsed AS (

SELECTDBName = DB_NAME(),

Owner = USER_NAME(so.UID),

TableName = so.Name,

TableID = so.ID,

MinRowSize = MIN(si.MinLen),

MaxRowSize = MAX(si.XMaxLen),

ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,

DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,

UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB

- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,

Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),

RowModCtr = MIN(si.RowModCtr),

HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),

HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)

FROM dbo.SysObjects so,

dbo.SysIndexes si,

(

SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte

FROM Master.dbo.spt_Values

WHERE Number = 1 --Identifies the primary row for the given type

AND Type = 'E' --Identifies row for system type

) pkb

WHERE si.ID = so.ID

AND si.IndID IN (0, --Table w/o Text or Image Data

1,

255) --Table w/ Text or Image Data

AND so.XType = 'U' --User Tables

AND PERMISSIONS(so.ID) <> 0

GROUP BY so.Name,

so.UID,

so.ID,

pkb.PageKB

HAVING SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END) > 100

)

SELECT DBName,

Owner = ISNULL(Owner,'dbo'),

Schema_Name = sch.name,

TableName,

ObjectName = QUOTENAME(DB_NAME()) + '.' + QUOTENAME(ISNULL(Owner,sch.name)) + '.' + QUOTENAME(TableName),

TableID,

MinRowSize,

MaxRowSize,

ReservedKB,

DataKB,

IndexKB,

UnusedKB,

Rows,

RowModCtr,

HasTextImage,

HasClustered

FROM SpaceUsed AS SU

CROSS APPLY ( select b.name

from sys.objects as a

inner join sys.schemas as b

on a.schema_id = b.schema_id

where a.object_id = su.TableID

) as sch

ORDER BY Rows Desc

-- Gianluca Sartori

balaji.ganga SSCrazy Points: 2280 More actions · Answer 2

Hi,

select a.name as objectname,b.rows from

sysobjects a

inner join sysindexes b on a.id=b.id

where indid>2

this query returns objects names and rows only.. if you want to find out the table size

you can use sp_spaceused 'tablename'

Thanks & Regards

Balaji.G

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

balaji.ganga (9/16/2010)
Hi,
select a.name as objectname,b.rows from
sysobjects a
inner join sysindexes b on a.id=b.id
where indid>2
this query returns objects names and rows only.. if you want to find out the table size
you can use sp_spaceused 'tablename'
Thanks & Regards
Balaji.G

Or, you could use the script already provided which does the equivalent of sp_spaceused for all tables at once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 4

... and 20 months later, there was a follow up reply :hehe:

Hope this helps,
Rock from VbCity

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Rock from VbCity (7/1/2012)
... and 20 months later, there was a follow up reply :hehe:

Was it wrong? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 6

Jeff Moden (7/1/2012)
Was it wrong? 😉

Absolutely not; sometimes postal services are slow :w00t: it was spot-on comment

Hope this helps,
Rock from VbCity

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Since essentially all tables\indexes in SQL Server 2005 on are partitioned, to get down to the partition sizes you'll need to join sys.indexes, sys.partitions and sys.allocation_units.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉