How to Find the number of rows,allocated size, rows size, index size and free space in a table

exec sp_spaceused 'AddressType'

name                  rows   reserved     data        index_size unused

AddressType         6        48 KB         8 KB       40 KB 0 KB

It returns number of rows, reserved space for the table, data size, index size and unused space left.

We can use above to find for one table.

When we tried to find all the tables in a database then we can achieve by writing t-SQL script

 --Declare the table variable to store the table names

DECLARE @Tables TABLE (SchemaTable varchar(100));

DECLARE @TableName varchar(100); --To store each table name

-- Insert table names into the table variable

INSERT @Tables (SchemaTable)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME

FROM INFORMATION_SCHEMA.tables

WHERE TABLE_TYPE = 'BASE TABLE'

--Loop through the table names

WHILE (SELECT COUNT(*) FROM @Tables) > 0

BEGIN

SELECT TOP 1 @TableName = SchemaTable

FROM @Tables

ORDER BY SchemaTable;

EXEC sp_spaceused @TableName;

DELETE @Tables

WHERE SchemaTable = @TableName;

END;

This will give the list of all the tables.