SQL2000 viewing the size of a table

 SQL2000 Viewing the size of a tableTags: sqlsql server database report Tableinsert2011-06-08 11:47 4013 People read comments (0) favorite reports

SQL2000 viewing the size of a table

This article from: http://blog.csdn.net/tianshikuqi8/archive/2011/01/04/6114682.aspx

SQL2000 viewing a table's size collection
Before looking at a huge database, want to delete the useless things inside, reduce the size of the database file, but do not know how or the size of one of the tables, and after some effort finally found how to view the size of the data table in SQL Server 2000 method, in SQL Server 2005, Microsoft has published a report that can be viewed directly, but this report cannot be used with SQL Server 2000.

The following statements are written to return each data table size:

CREATE TABLE TMP (name varchar), rows int,reserved varchar (50),
Data varchar, index_size varchar (+), 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

--Look at a single table, taking up data
sp_spaceused ' Retail11 '

--View all the tables in the entire database take up space but find a table from sysindexes, there will be a small number of data tables (no primary key, no index table)
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 all the tables in the entire database take up space but it looks like a bit of a hassle.
exec sp_msforeachtable "exec sp_spaceused '?"

SQL2000 viewing the size of a table