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
Reference: Pinal Dave (http://www.SQLAuthority.com)
277 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
write an SQL query that returns the sum of the numbers in column v.
For example, given:
v
—
2
10
20
10
your query should return 42.
Code please
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