Blog

USE abcdb
 
DECLARE @TABLE 
  TABLE(Id INT IDENTITY(1,1), Name VARCHAR(256))
 
INSERT INTO @TABLE
  SELECT b.name + '.'+ a.name
  FROM sys.tables a INNER JOIN sys.schemas b
  ON a.schema_id = b.schema_id
 
INSERT INTO @TABLE
SELECT '-1'
 
DECLARE @RESULT TABLE(TableName VARCHAR(256)
, TotalRows INT
, Reserved VARCHAR(50)
, DataSize VARCHAR(50)
, IndexSize VARCHAR(50)
, UnusedSize VARCHAR(50))
 
DECLARE @temp VARCHAR(256)
DECLARE @INDEX INT
SET @INDEX = 1
 
WHILE 1=1
BEGIN
  SELECT @temp = Name FROM @TABLE
  WHERE Id = @INDEX
 
  IF @temp = '-1' BREAK	
 
  INSERT @RESULT(TableName
  , TotalRows
  , Reserved
  , DataSize
  , IndexSize
  , UnusedSize)
  EXEC sp_spaceused @temp
 
  SET @INDEX = @INDEX + 1
END
 
SELECT c.name+'.'+b.name AS [TABLE], a.*
FROM @RESULT a
INNER JOIN sys.tables b ON a.TableName = b.name
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC