In SQL Server 2005, Microsoft released a report that can be viewed directly. However, this report cannot be used in SQL Server 2000.
So I wrote the following sentence to return the same result.
Create Table TMP (name varchar (50), rows int, reserved varchar (50 ),
Data varchar (50), index_size varchar (50), unused varchar (50 ))
Insert into TMP (name, rows, reserved,
Data, index_size, unused) exec sp_msforeachtable @ command1 = "sp_spaceused '? '"-- Sp_spaceused 't_ vehicle'
Select * from TMP order by data DESC
Drop table TMP
-- View the data usage of a single table
Sp_spaceused 'retail11'
-- View the space occupied by all the tables in the database. However, if a table is retrieved from sysindexes, a portion of the data tables are missing (tables without primary keys and indexes)
Select object_name (ID) tablename, 8 * Reserved/1024 reserved, rtrim (8 * dpages/1024) + 'mb' used, 8*(Reserved-dpages)/1024 unused, 8 * dpages/1024-rows/1024 * minlen/1024 free,
Rows, * From sysindexes
Where indid = 1
Order by reserved DESC
-- View the space occupied by all the tables in the database, but it looks troublesome.
Exec sp_msforeachtable "Exec sp_spaceused '? '"
Source: http://www.cnitblog.com/yhf119/archive/2007/10/30/35606.html
In SQL Server 2005, Microsoft released a report that can be viewed directly. However, this report cannot be used in SQL Server 2000.
So I wrote the following sentence to return the same result.
Create Table TMP (name varchar (50), rows int, reserved varchar (50 ),
Data varchar (50), index_size varchar (50), unused varchar (50 ))
Insert into TMP (name, rows, reserved,
Data, index_size, unused) exec sp_msforeachtable @ command1 = "sp_spaceused '? '"-- Sp_spaceused 't_ vehicle'
Select * from TMP order by data DESC
Drop table TMP
-- View the data usage of a single table
Sp_spaceused 'retail11'
-- View the space occupied by all the tables in the database. However, if a table is retrieved from sysindexes, a portion of the data tables are missing (tables without primary keys and indexes)
Select object_name (ID) tablename, 8 * Reserved/1024 reserved, rtrim (8 * dpages/1024) + 'mb' used, 8*(Reserved-dpages)/1024 unused, 8 * dpages/1024-rows/1024 * minlen/1024 free,
Rows, * From sysindexes
Where indid = 1
Order by reserved DESC
-- View the space occupied by all the tables in the database, but it looks troublesome.
Exec sp_msforeachtable "Exec sp_spaceused '? '"
Source: http://www.cnitblog.com/yhf119/archive/2007/10/30/35606.html