How to size table by number of rows in SQL Server

Question

I have a table where I want to get size of the number records based on byte In SQL Server:

Do you want to know the size of given table in bytes or you want to count the number of records. — Pரதீப், Jan 16 '16 at 08:11
Have a look at this question http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database — Pரதீப், Jan 16 '16 at 08:19
Please rewrite the question as this is not clear enough to understand. — shadow, Jan 16 '16 at 09:13
**I ask the question a different way Want to condition number record gain Then their weight gain **
--How Size table By Condications ==>select * from dbo.PictureTable where SchoolCode=1001<== — mojtaba khooshrou, Jan 16 '16 at 09:32
IMO, A result of a query is a `View` not a `Table` and a View has no space over your storage !? — shA.t, Jan 16 '16 at 12:20
** I know, but I want to know the number of records Weight table Not All Records Table ** — mojtaba khooshrou, Jan 17 '16 at 13:58

score 0 · Answer 1 · edited May 23 '17 at 12:09

The only answer related to this question that I can suggest is this:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    p.rows AS RowCounts,
    a.total_pages * 8 AS TotalSpaceKB,
    a.used_pages * 8 AS UsedSpaceKB,
    a.total_pages * 8 / p.rows AS eachRowTotalSpaceKB,
    a.used_pages * 8 / p.rows AS eachRowUsedSpaceKB,
    a.total_pages * 8 / p.rows * (select * from dbo.PictureTable where SchoolCode=1001) AS queryTotalSpaceKB,
    a.used_pages * 8 / p.rows * (select * from dbo.PictureTable where SchoolCode=1001) AS queryTotalSpaceKB
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 
    s.name = 'dbo' AND
    t.name = 'PictureTable';

Edit
You can get length of your data in a field by using DATALENGTH like this:

SELECT SUM(DATALEGTH(PictureTable_ID)) + SUM(DATALEGTH(Groupcourses_ID)) +SUM(DATALEGTH(per_ID)) + SUM(DATALEGTH(School_ID)) + SUM(DATALEGTH(AcademicYear_ID)) + SUM(DATALEGTH(PictureTable_Description)) + SUM(DATALEGTH(SchoolCode)) As rowSizes
FROM PictureTable
WHERE (SchoolCode = 1001);

Is All Weight table -- I Went Custom Number Records Weight In Table — mojtaba khooshrou, Jan 17 '16 at 14:00
If you want to know the data length of each row I add an edited info to my answer ;). — shA.t, Jan 18 '16 at 05:08

How to size table by number of rows in SQL Server

1 Answers1