September 14, 2010 at 11:13 pm
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
September 15, 2010 at 1:32 am
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 (
SELECT DBName = 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
September 16, 2010 at 5:56 am
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
June 30, 2012 at 5:11 pm
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
July 1, 2012 at 8:07 am
... and 20 months later, there was a follow up reply :hehe:
Hope this helps,
Rock from VbCity
July 1, 2012 at 10:36 am
Rock from VbCity (7/1/2012)
... and 20 months later, there was a follow up reply :hehe:
Was it wrong? 😉
--Jeff Moden
July 1, 2012 at 7:25 pm
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
July 2, 2012 at 12:02 pm
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" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply