January 30, 2014 at 9:12 pm
Comments posted to this topic are about the item Calculate Table Size and row count
January 31, 2014 at 11:02 am
Improved version!! I added Schema, since we can have more than 1 table with the same name in the DB.
Note: We don't need the temporary table as a placeholder.
In any case if needed, DO NOT use the "SELECT * into #temp_table1"; For bigger DATASETS, this cmd is BAD; It'll create a lockout in Tempdb.
The best way is to create the place holder via DDL (ie. SELECT * into #temp_table1 where 1 = 2) Once the placeholder is created as #temp_table1
Then issue the insert stmnt :
INSERT #temp_table1
SELECT * FROM WhatEverquery
This will save you lots of headaches
here's the improved version which can also b Parameterized as a Sproc if needed
SELECT s.name +'.' + B.name, CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024)) as [Table Used Size(MB)],
CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) as [Table Located Size(MB)],
(CONVERT(numeric(30,3),(CONVERT(float, SUM(A.reserved_page_count)*8)/1024)) - CONVERT(numeric(30,3),(CONVERT(float, SUM(A.used_page_count)*8)/1024))) as [Table Free Size(MB)] ,
A.row_count
from sys.dm_db_partition_stats A join sys.all_objects B
ON A.object_id = B.object_id
join sys.schemas S
on B.schema_id = S.schema_id
where B.type != 'S'
group by s.name +'.' + B.name, row_count
order by s.name +'.' + B.name asc
January 31, 2014 at 4:13 pm
These don't work with partitioned tables or indexes. It lists everything on separate rows.
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
February 14, 2014 at 6:47 pm
Here is an alternative that gives a bit more info.
http://jasonbrimhall.info/2011/11/21/table-space-cs-part-deux/
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply