How to determine the size of my tables in the SQL Server database?

595    Asked by AnushaAcharya in SQL Server , Asked on Apr 22, 2021

Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?

Answered by Ankur vaish

For SQL server show tables, you can use sp_spaceused MyTable For all tables in a database, you can use it with sp_msforeachtable as following

CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_default GROUP BY a.table_name, a.row_count, a.data_size ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC DROP TABLE #temp
Hope this helps you resolve SQL server show tables!

Your Answer

Interviews

Parent Categories