August 6, 2018 at 11:09 am
Comments posted to this topic are about the item Table Space Used in DB
August 9, 2018 at 1:48 pm
Nice article, but in MS SQL Server is preferred to avoid courses. With the System Dynamic Management Views this is now a hard to write select statement that return size for all tables in database.
For example:
;with tbl_pages_counts as
(
select object_id, index_id, row_count, used_page_count, reserved_page_count, fp.free_page_count
from sys.dm_db_partition_stats
cross apply ( select reserved_page_count - used_page_count as free_page_count ) as fp
)
select B.name
, A.row_count
, 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.free_page_count)*8)/1024)) as [Table Free Size(MB)]
from tbl_pages_counts as A
inner join sys.all_objects as B
on A.object_id = B.object_id and B.type = N'U'
group by name, row_count
order by [Table Located Size(MB)] desc
-- order by A.row_count desc
-- order by B.name
go
August 9, 2018 at 4:18 pm
Your script is good, however you always need to ensure you include schema in queries like this, when you don't, you run the risk of similarly named objects grouping together and giving a false picture when there are multiple schema's involved, otherwise your script is only good for single schema'd databases.
Using your example, this would be
;with TBL_PAGES_COUNTS as
(
SELECT
Object_ID
,Index_ID
,Row_Count
,Used_Page_Count
,Reserved_Page_Count
,FP.Free_Page_Count
FROM
SYS.DM_DB_PARTITION_STATS
CROSS APPLY (SELECT Reserved_Page_Count - Used_Page_Count as Free_Page_Count) as FP
)
SELECT
SCHEMA_NAME(SCHEMA_ID) as SchemaName
,B.Name as TableName
,A.Row_Count
,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.Free_Page_Count)*8)/1024)) as [Table Free Size(MB)]
FROM
TBL_PAGES_COUNTS as A
INNER JOIN SYS.ALL_OBJECTS AS B ON A.OBJECT_ID = B.OBJECT_ID AND B.TYPE = N'U'
GROUP BY
B.Name
,B.Schema_ID
,A.Row_Count
ORDER BY [Table Located Size(MB)] DESC
-- ORDER BY A.Row_Count DESC
-- ORDER BY B.Name
go
August 14, 2018 at 9:37 am
Psst...we can still see your table names, not sure if you're okay with that. It's not 100% opaque..
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
August 14, 2018 at 6:49 pm
GabyYYZ - Tuesday, August 14, 2018 9:37 AMPsst...we can still see your table names, not sure if you're okay with that. It's not 100% opaque..
Yup, I know, I did that on purpose. It's all good
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply