This is a very handy script, Thank you very much for it. I updated the script to handle tables in different schema's. I was using this today and found I have issues when the tables had different schema's.

--

use YourDatabaseName

go

--SELECT * FROM sys.Tables

--SELECT @@SERVERNAME;

--SELECT DB_NAME() AS DataBaseName;

DECLARE @names TABLE

(

id INT IDENTITY(1, 1)

, name NVARCHAR(100) NULL

);

DECLARE @ROWCOUNT INT;

DECLARE @i INT = 1;

DECLARE @STR NVARCHAR(100);

INSERT INTO @names

( name

)

SELECT SCHEMA_NAME(schema_id) + '.' + name

FROM sys.tables;

--INSERT INTO @names(name) SELECT name FROM sys.Tables RebuttalStatusHistory ReviewHistory

SET @ROWCOUNT = @@ROWCOUNT;

DECLARE @space TABLE

(

name NVARCHAR(100) NULL

, rows CHAR(11)

, reserved NVARCHAR(15)

, data NVARCHAR(18)

, indexes NVARCHAR(18)

, unused NVARCHAR(18)

);

WHILE @i <= @ROWCOUNT

BEGIN

SELECT @STR = name

FROM @names

WHERE id = @i;

INSERT INTO @space

EXEC sp_spaceused @STR;

SET @i += 1;

END;

SELECT *

FROM @space

ORDER BY CONVERT(BIGINT, rows) DESC;