![clock](../themes/indigo/img/timeicon.gif)
July 3, 2024 07:44 by
Peter
//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
![](https://europeanwindowshosting.hostforlife.eu/image.axd?picture=2015%2f4%2fhostforlife+banner.png)