Compare Page Revisions
« Older Revision - Back to Page History - Current Revision
TableSize
with RowQtys as ( SELECT TableName = '[' + s.name + '].[' + o.name + ']' ,RowQty = sum(p.rows) FROM sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id inner join sys.partitions p on p.object_id = o.object_id and o.type = 'U' LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE 1=1 -- 0 = heap table, 1 = table with clustered index and p.index_id in (0, 1) and a.type = 1 -- row-data only , not LOB and p.rows is not null group by o.name, s.name ) ,ColumnSizes as ( select distinct TableName = '[' + s.name + '].[' + o.name + ']' ,ColumnSize = 1 + case t.name when 'bigint' then 8 when 'int' then 4 when 'smallint' then 2 when 'tinyint' then 1 when 'bit' then 1 when 'numeric' then 19 when 'money' then 8 when 'float' then 8 when 'real' then 8 when 'datetime' then 8 when 'nchar' then 2 * c.max_length when 'nvarchar' then 2 * c.max_length when 'ntext' then 2 * c.max_length when 'binary' then 510 when 'varbinary' then 510 when 'uniqueidentifier' then 16 end from sys.objects o inner join sys.columns c on o.object_id = c.object_id inner join sys.types t on t.system_type_id = c.system_type_id inner join sys.schemas s on o.schema_id = s.schema_id where 1=1 and o.type = 'u' and t.name not in ('sysname') and o.name not in ('dtproperties') and o.name not like 'sys%' and o.name not like 'z[_]DeadCode[_]%' ) select cs.TableName ,RowSize = sum(cs.ColumnSize) + 6 ,rc.RowQty ,TableSize = (sum(cs.ColumnSize) + 6) * rc.RowQty from ColumnSizes cs left join RowQtys rc on cs.TableName = rc.TableName group by cs.TableName, rc.RowQty ORDER BY cs.TableName
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.