Comments posted to this topic are about the item Calculate Table Size and row count
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 :
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)] ,
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
These don't work with partitioned tables or indexes. It lists everything on separate rows.
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
Here is an alternative that gives a bit more info.
I have given a name to my pain...MCM SQL Server, MVP
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)