Best Way To Find Table Wise Rows And Size In Sql

This is a simple stored procedure in which you can search through all the data (table wise rows and size) in the SQL Server database. You can see all information like column name, schema name, row count & row size of a single table.


SELECT T.NAME AS TableName, S.Name AS SchemaName, P.rows AS RowCounts, SUM(A.total_pages) * 8 AS TotalSpaceKB,SUM(a.total_pages) * 8 / 1024 as [TotalSpaceMB], 
SUM(A.used_pages) * 8 AS UsedSpaceKB, sum(a.used_pages) * 8 / 1024 as [UsedSpaceMB], (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

SELECT T.NAME AS TableName, S.Name AS SchemaName, P.rows AS RowCounts, SUM(A.total_pages) * 8 AS TotalSpaceKB,SUM(a.total_pages) * 8 / 1024 as [TotalSpaceMB],