Its very important to know your database stats while planning for some architectural update or database migration. The beforehand information would always add a plus to mitigate any risk and proactively take actions.

Below is SQL Server script that provides information about database stats which include list of all tables, there respective record count, allocated size and so on.

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 --Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR FOR 
	SELECT [name] AS SchemaTable
	FROM sys.all_objects o 
	WHERE [type] = 'U'
	FOR READ ONLY

 --A procedure level temp table to store the results
CREATE TABLE #TempTable
(
     id int identity,
     tableName varchar(100),
     numberofRows varchar(100),
     reservedSize varchar(50),
     dataSize varchar(50),
     indexSize varchar(50),
     unusedSize varchar(50)
 )

--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
     --Dump the results of the sp_spaceused query to the temp table
     INSERT  #TempTable
         EXEC sp_spaceused @TableName

     UPDATE #TempTable set tableName = @TableName where id = @@identity 

     --Get the next table name
     FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the results
SELECT * 
FROM #TempTable
ORDER BY tableName

--Final cleanup!
DROP TABLE #TempTable
GO

The above script uses in build stored procedure of SQL Server database engine “sp_spaceused” to accomplish the task.