-- Shows a list of all EventSentry tables in database, sorted by used size in bytes
-----------------------------------------------------------------------------------
-- Field Explanation:
-- name: Name of table
-- rows: Number of rows
-- reserved: Reserved bytes (= data + index_size + unused)
-- data: Number of used bytes
-- index_size: Size of all indexes
-- unused: Number of unused bytes 

CREATE TABLE #TempTableSize
(
  name varchar(255),
  rows int,
  reserved varchar(25),
  data varchar(25),
  index_size varchar(25),
  unused varchar(25)
)

DECLARE tables_cursor CURSOR
	FOR
	SELECT distinct name FROM sysobjects WHERE type = 'U' and name like 'ES%'
OPEN tables_cursor

DECLARE @tablename sysname

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	INSERT INTO #TempTableSize
	EXEC sp_spaceused @tablename
	FETCH NEXT FROM tables_cursor INTO @tablename
END

DEALLOCATE tables_cursor

SELECT
	name, 
	CAST(rows AS int) AS rows, 
	CAST(RTRIM(SUBSTRING(reserved, 1, CHARINDEX('KB', reserved) -1)) AS int) AS reserved, 
	CAST(RTRIM(SUBSTRING(data, 1, CHARINDEX('KB', data) -1)) AS int) AS data, 
	CAST(RTRIM(SUBSTRING(index_size, 1, CHARINDEX('KB', index_size) -1)) AS int) AS index_size, 
	CAST(RTRIM(SUBSTRING(unused,1, CHARINDEX('KB', unused) -1)) AS int) AS unused
INTO #TempTableSize2
FROM #TempTableSize

DROP TABLE #TempTableSize

SELECT * FROM #TempTableSize2 ORDER BY reserved DESC

DROP TABLE #TempTableSize2