USE DatabaseName
GO
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
270 Comments. Leave new
Hi dave
Is there any way to Find the Space of All table by filtering the Row.
Like I have Foreign Key ‘Company ID ‘ In All Table would it be possible if i want to know the space Occupied by Company
Thanks
Junaid
How can I get size of a column in a table ? I mean if I want to know, size of some particular column is there any command like sp_spaceused ? I believe sp_Spaceused give size of only table and not particular Column.
SELECT * FROM TB_NAME
ORDER BY COLUMN NAME
OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY