January 29, 2014 at 2:48 am
Comments posted to this topic are about the item Total space used per table
February 3, 2014 at 7:05 am
Nice script! Thank you.
I changed it a bit (for my purposes) to express the sizes in MBs since it's easier for reading when run on big databases.
SELECT
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id) AS NAME,
CONVERT(decimal(10,2),SUM(reserved_page_count) * 8/1024.0) AS total_space_used_MB,
CONVERT(decimal(10,2),SUM(CASE WHEN index_id < 2 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS table_space_used_MB,
CONVERT(decimal(10,2),SUM(CASE WHEN index_id > 1 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS nonclustered_index_spaced_used_MB,
MAX(row_count) AS row_count
FROM
sys.dm_db_partition_stats AS p
INNER JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY
total_space_used_MB desc
Regards,
Igor Micev
Igor Micev,My blog: www.igormicev.com
February 11, 2014 at 10:05 am
total table space means data size plus index size?
February 11, 2014 at 10:39 am
Ron007 (2/11/2014)
total table space means data size plus index size?
Yes.
The author uses the reserved_page_count for both data and indexes.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
February 14, 2014 at 6:45 pm
Here is an alternative for getting more info about table size, index size and overall size.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 23, 2015 at 1:47 pm
Thank you 🙂
May 5, 2015 at 3:05 pm
Had a lot of fun with this one. We found one particular table we are now going to trim out the old history.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply