Skip to main content

Posts

Showing posts from August, 2018

Useful SQL Query Snippets: Table Sizes

I can't count the number of times I've used this query at various sites. It started when I needed to use

As with many things these days, I found it on StackOverflow but I want to document it here in case it ever goes missing.

While it makes use of the sys. tables (which numerous articles say may change in the future ) as opposed to INFORMATION_SCHEMA, the end result compares the size of tables while using the partitions and tables for calculating sizes.

We use this often for identifying which tables need to be cleared out or reduced when creating QA or test environments that are limited in environments due to size. Our production system is well over 200GB and if I need to do quick development, copying a DB that size is just dumb. This query lets me see where I need to "trim the fat".

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) A…