When you need to understand where which tables are consuming the most space in your database, the following script will come in handy.


DECLARE  @SourceDB VARCHAR(50)

SET @SourceDB = 'DatabaseName'
SET NOCOUNT ON

DECLARE  @sql VARCHAR(128)

CREATE TABLE #TABLES (
  NAME VARCHAR(128))

SELECT @sql = 'insert #tables select TABLE_NAME from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''

EXEC( @sql)

CREATE TABLE #SPACEUSED (
  NAME       VARCHAR(128),
  ROWS       INT,
  RESERVED   VARCHAR(18),
  DATA       VARCHAR(18),
  INDEX_SIZE VARCHAR(18),
  UNUSED     VARCHAR(18))

DECLARE  @name VARCHAR(128)

SELECT @name = ''

WHILE EXISTS (SELECT *
              FROM   #TABLES
              WHERE  NAME > @name)
  BEGIN
    SELECT @name = MIN(NAME)
    FROM   #TABLES
    WHERE  NAME > @name
    
    SELECT @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused [' + @name + ']'''
    
    EXEC( @sql)
  END

SELECT   *
FROM     #SPACEUSED
ORDER BY ROWS DESC

DROP TABLE #TABLES

DROP TABLE #SPACEUSED