June 10, 2014 at 10:07 am
some one confirm that - It is possible to find table size and in that table each row size.
June 10, 2014 at 10:15 am
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Thanks:-)
Viresh
--------------------------------------------------------------------------
“ The future belongs to those who are virile, to whom it is a pleasure to live, to create, to whet their intelligence on that of the others. ”
— Sir Henri Deterding
June 11, 2014 at 7:38 am
viresh29 (6/10/2014)
SELECTt.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Sorry to nit pick here, but is this code easily readable to you?:-) See the Pet Peeve post. 😀
Here is the same code only 'formatted'. Well done viresh29.
SELECT t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON
t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON
i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON
p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON
t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY t.Name
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 11, 2014 at 8:03 am
As for the requirement to find row size....
If all your columns have fixed-width data types such as int, datetime and char(100), just divide the table size by the row count for an approximate value (approximate because it doesn't take into account any free space left on the data pages). For a more accurate value, add the widths of each column together (so in the example above, it would be 4 + 8 + 100).
If any of your columns have variable-width data types such as varchar(100), it's possible, but not really worth the effort, in my opinion. You have to use a function such as LEN or DATALENGTH on each variable-width column in each row.
John
June 11, 2014 at 8:31 am
You can use sys.dm_db_index_physical_stats in the "DETAILED" mode to find the min, max, and average row size of clustered index rows, which is the table data itself. LOB data will be listed separately and can be a bit confusing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply