Examining table size

The SQL Server system stored procedure sp_spaceused reports information about a table that can be useful in forming and implementing a data retention plan.
Note: This topic applies only to on-premises environments.

The stored procedure shows this information:

  • Number of rows in a table
  • Space reserved for a table
  • Space used by data in a table
  • Space used by the index in a table
  • Unused space in a table
In SQL Server Management Studio, with the Mongoose application database selected as the current database, use this syntax to generate a report on a table: 
EXEC sp_spaceused table_name

For example: 

EXEC sp_spaceused ledger

To report on tables that are likely to need attention in a data retention plan, you can use this script: 

-- Ledger          
EXEC sp_spaceused ledger
EXEC sp_spaceused ledger_all

-- Material Transaction  
EXEC sp_spaceused matltran
EXEC sp_spaceused matltran_all
EXEC sp_spaceused matltran_amt
EXEC sp_spaceused matltran_amt_all

-- Job Transactions    
EXEC sp_spaceused jobtran

-- AR Transactions     
EXEC sp_spaceused artran
EXEC sp_spaceused artran_all

-- AP Transactions     
EXEC sp_spaceused aptrxp
EXEC sp_spaceused aptrxp_all

-- Audit Logs
EXEC sp_spaceused AuditLog

To report on all tables in the database, you can use this script: 

DECLARE @table_name sysname
DECLARE Tables_Cursor CURSOR FOR
SELECT name 
FROM sysobjects
WHERE type = 'U' ORDER BY 1

OPEN Tables_Cursor

FETCH NEXT FROM Tables_Cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC sp_spaceused @table_name
  FETCH NEXT FROM Tables_Cursor
  INTO @table_name
END

CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor

To select tables with similar names, modify the WHERE clause in the script. For example, to report only on tables with the _all suffix, replace this clause: 

WHERE type = 'U' ORDER BY 1

With this clause: 

WHERE type = 'U' AND name LIKE '%[_]all' ORDER BY 1

For more information about sp_spaceused, see the SQL Server help.

Related topics