//create Temporary Table

CREATE TABLE #Table_Details (
    table_name sysname,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50)
);
INSERT INTO #Table_Details
EXEC sp_msforeachtable 'sp_spaceused ''?''';

sp_MSforeachtable is a system-stored procedure in Microsoft SQL Server that allows you to execute a specified command against each table in a database.

Ensure that the command you are executing is appropriate for all tables, as sp_MSforeachtable does not check if the command is valid for each individual table.

EXEC sp_spaceused ''?'': The sp_spaceused stored procedure is called for each table. The ? is a placeholder that gets replaced with the table name.
SELECT
    TD.table_name,
    TD.row_count,
    COUNT(*) AS col_count,
    TD.data_size
FROM
    #Table_Details TD
INNER JOIN
    information_schema.columns b ON TD.table_name COLLATE database_default = b.table_name COLLATE database_default
GROUP BY
    TD.table_name,
    TD.row_count,
    TD.data_size
ORDER BY
    CAST(REPLACE(TD.data_size, ' KB', '') AS INT) DESC;
DROP TABLE #Table_Details;

Order By is used to get the biggest table first.

HostForLIFEASP.NET SQL Server 2022 Hosting