The company I work with had it's DB double its size lately, so I needed to find out which tables were the biggest.

I found this on the web, and decided it's worth remembering!

Taken from http://www.sqlteam.com/article/finding-the-biggest-tables-in-a-database, the code is from http://www.sqlteam.com/downloads/BigTables.sql

/**************************************************************************************
*
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  [email protected]
*  v1.1
*
**************************************************************************************/
DECLARE @id INT
DECLARE @type CHARACTER(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize DEC(15, 0)
DECLARE @bytesperpage DEC(15, 0)
DECLARE @pagesperMB DEC(15, 0)

CREATE TABLE #spt_space
  (
     objid    INT NULL,
     ROWS     INT NULL,
     reserved DEC(15) NULL,
     data     DEC(15) NULL,
     indexp   DEC(15) NULL,
     unused   DEC(15) NULL
  )

SET nocount ON

-- Create a cursor to loop through the user tables
DECLARE c_tables CURSOR FOR
  SELECT id
  FROM   sysobjects
  WHERE  xtype = 'U'

OPEN c_tables

FETCH NEXT FROM c_tables INTO @id

WHILE @@FETCH_STATUS = 0
  BEGIN
      /* Code from sp_spaceused */
      INSERT INTO #spt_space
                  (objid,
                   reserved)
      SELECT objid = @id,
             SUM(reserved)
      FROM   sysindexes
      WHERE  indid IN ( 0, 1, 255 )
             AND id = @id

      SELECT @pages = SUM(dpages)
      FROM   sysindexes
      WHERE  indid < 2
             AND id = @id

      SELECT @pages = @pages + Isnull(SUM(used), 0)
      FROM   sysindexes
      WHERE  indid = 255
             AND id = @id

      UPDATE #spt_space
      SET    data = @pages
      WHERE  objid = @id

      /* index: sum(used) where indid in (0, 1, 255) - data */
      UPDATE #spt_space
      SET    indexp = (SELECT SUM(used)
                       FROM   sysindexes
                       WHERE  indid IN ( 0, 1, 255 )
                              AND id = @id) - data
      WHERE  objid = @id

      /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
      UPDATE #spt_space
      SET    unused = reserved - (SELECT SUM(used)
                                  FROM   sysindexes
                                  WHERE  indid IN ( 0, 1, 255 )
                                         AND id = @id)
      WHERE  objid = @id

      UPDATE #spt_space
      SET    ROWS = i.ROWS
      FROM   sysindexes i
      WHERE  i.indid < 2
             AND i.id = @id
             AND objid = @id

      FETCH NEXT FROM c_tables INTO @id
  END

SELECT TOP 25 table_name = (SELECT LEFT(name, 25)
                            FROM   sysobjects
                            WHERE  id = objid),
              ROWS = CONVERT(CHAR(11), ROWS),
              reserved_kb = Ltrim(Str(reserved * d.low / 1024., 15, 0) + ' ' + 'KB'),
              data_kb = Ltrim(Str(data * d.low / 1024., 15, 0) + ' ' + 'KB'),
              index_size_kb = Ltrim(Str(indexp * d.low / 1024., 15, 0) + ' ' + 'KB'),
              unused_kb = Ltrim(Str(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM   #spt_space,
       MASTER.dbo.spt_values d
WHERE  d.NUMBER = 1
       AND d.TYPE = 'E'
ORDER  BY reserved DESC

DROP TABLE #spt_space

CLOSE c_tables

DEALLOCATE c_tables