Problem
- How big are my tables?
- How big is the data?
- How big are the indexes?
Solution 1: MS SQL Server stored procedures
- Microsoft SQL Server are stored procedure that will do that.
- The layout is not that great and makes it more difficult to use.
- USE sql911;
- go
- EXEC sp_msforeachtable 'sp_spaceused "?"';;
- go
name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ EMP_WORK 75 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ DEPARTMENT 9 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ ORG 10 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ sales 39 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ PROJECT 20 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ sales02 41 16 KB 8 KB 8 KB 0 KB name rows reserved data index_size unused -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------ Emps 32 16 KB 8 KB 8 KB 0 KB
- You must first select the database.
- You do not control the order of the display
Solution 2: Query the SYSINDEXES file
- The SYSINDEXES file keeps tracks of all information.
- The size are the number of pages, and each page is 8Kb, so they need to be converted.
- USE sql911;
- go
- SELECT CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
- CAST(rowcnt AS VARCHAR(6)) AS '#Rows',
- reserved * 8 AS 'Disk - Kb',
- dpages * 8 AS 'Data - Kb',
- (SUM(used) * 8) - (dpages * 8) AS 'Index - Kb'
- FROM sysindexes
- WHERE indid IN (0,1)
- AND OBJECTPROPERTY(id, 'IsUserTable') = 1
- GROUP BY id, rowcnt, reserved, dpages
- ORDER BY 'Table';
- go
Table #Rows Disk - Kb Data - Kb Index - Kb ------------ ------ ----------- ----------- ----------- DEPARTMENT 9 16 8 8 EMP_WORK 75 16 8 8 Emps 32 16 8 8 ORG 10 16 8 8 PROJECT 20 16 8 8 sales 39 16 8 8 sales02 41 16 8 8 (7 row(s) affected)
- You must first select the database.
- SYSINDEXES store the space allocated in pages.
- The standard page size is 8kb, unless you created the database with a different page size, then adjust the query.