Hey, great tip!  It helped a lot.  I made a minor mod below which simply rips out the ' KB' and casts the data as INT.  Then, I sort the list by Reserved DESC since I am mostly trying to find which tables are taking up the most space.  I also wrapped into a SPROC for easy access.  Here is the changes below.  Again, thank you.  Saved me some time...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ListTableSizes
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 BEGIN try 
  DECLARE @table_name VARCHAR(500) ; 
  DECLARE @schema_name VARCHAR(500) ; 
  DECLARE @tab1 TABLE(
   tablename VARCHAR (500) collate database_default
   , schemaname VARCHAR(500) collate database_default
  ); 
  DECLARE  @temp_table TABLE (    
   tablename sysname
   , row_count INT
   , reserved VARCHAR(50) collate database_default
   , data VARCHAR(50) collate database_default
   , index_size VARCHAR(50) collate database_default
   , unused VARCHAR(50) collate database_default 
  ); 

  INSERT INTO @tab1 
   SELECT
    t1.name
    , t2.name 
   FROM sys.tables t1 
   INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

  DECLARE c1 CURSOR FOR 
   SELECT t2.name + '.' + t1.name  
   FROM sys.tables t1 
   INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

  OPEN c1; 
  FETCH NEXT FROM c1 INTO @table_name;
  WHILE @@FETCH_STATUS = 0 
  BEGIN  
   SET @table_name = REPLACE(@table_name, '[',''); 
   SET @table_name = REPLACE(@table_name, ']',''); 

   -- make sure the object exists before calling sp_spacedused
   IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
   BEGIN
    INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
   END
   
   FETCH NEXT FROM c1 INTO @table_name; 
  END; 
  CLOSE c1; 
  DEALLOCATE c1;

  SELECT
   t1.tablename AS TableName
   , t1.row_count AS TableRows
   , CAST(REPLACE(t1.reserved, ' KB', '') AS INT) AS ReservedSpace
   , CAST(REPLACE(t1.data, ' KB', '') AS INT) AS DataSize
   , CAST(REPLACE(t1.index_size, ' KB', '') AS INT) AS IndexSize
   , CAST(REPLACE(t1.unused, ' KB', '') AS INT) AS UnusedSpace
   , t2.schemaname AS SchemaName
  FROM @temp_table t1 
  INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
  ORDER BY CAST(REPLACE(t1.reserved, ' KB', '') AS INT) desc;

 END try 
 BEGIN catch 
  SELECT -100 AS l1
  , ERROR_NUMBER() AS tablename
  , ERROR_SEVERITY() AS row_count
  , ERROR_STATE() AS reserved
  , ERROR_MESSAGE() AS data
  , 1 AS index_size, 1 AS unused, 1 AS schemaname 
 END catch
END
GO

Or how about ... ? 

-- Show the size of all the tables in a database
declare @cmd1 varchar(500)

set @cmd1 =
 'exec sp_spaceused ''?'''

exec sp_msforeachtable @command1=@cmd1

 Here's another version. The original didn't work properly with repeated table names (i.e. schema1.table, schema2.table would result in more than 2 rows of output). This one also sorts by descending size.

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN  
        SET @table_name = REPLACE(@table_name, '[','');
        SET @table_name = REPLACE(@table_name, ']','');

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN                
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
        
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

SELECT tablename, row_count, reserved, data, index_size, unused
    FROM @temp_table ORDER BY CONVERT(INT,REPLACE(data,'KB','')) DESC;

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catch

This is briliant I have to say. Im just wondering if I can create procedure with will save those data into regular table with date of execution - like snapshot in time and save file sizes as numbers. Then I can make charts how DB was progressing and very nice other stuff. Can anybody help we with this task, Im not really database guy :(

Well, I tried my best and this is what I came up with:

USE SECREADER

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (   
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name 
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );  

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN 
        SET @table_name = REPLACE(@table_name, '[','');
        SET @table_name = REPLACE(@table_name, ']','');

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN               
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
       
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

INSERT INTO [DbStatistics] ( DateReporting, TableName, [RowCount], Reserved, Data, IndexSize, Unused )
SELECT GETDATE() as DateReporting, tablename as TableName, row_count as [RowCount], CONVERT(INT,REPLACE(reserved,'KB','')) as Reserved, CONVERT(INT,REPLACE(data,'KB','')) as Data, CONVERT(INT,REPLACE(index_size,'KB','')) as IndexSize, CONVERT(INT,REPLACE(unused,'KB','')) as Unused
FROM @temp_table;

SELECT 1

 

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catchUSE SECREADER

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE  @temp_table TABLE (   
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default
,        row_id INT IDENTITY(1,1)
);

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name 
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );  

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN 
        SET @table_name = REPLACE(@table_name, '[','');
        SET @table_name = REPLACE(@table_name, ']','');

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN               
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
                UPDATE @temp_table SET tablename = @table_name WHERE row_id =
                    (SELECT MAX(row_id) FROM @temp_table)
        END
       
        FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;

INSERT INTO [DbStatistics] ( DateReporting, TableName, [RowCount], Reserved, Data, IndexSize, Unused )
SELECT GETDATE() as DateReporting, tablename as TableName, row_count as [RowCount], CONVERT(INT,REPLACE(reserved,'KB','')) as Reserved, CONVERT(INT,REPLACE(data,'KB','')) as Data, CONVERT(INT,REPLACE(index_size,'KB','')) as IndexSize, CONVERT(INT,REPLACE(unused,'KB','')) as Unused
FROM @temp_table;

SELECT 1

 

END try
BEGIN catch
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname
END catch

It work with table

CREATE TABLE [dbo].[DbStatistics](
    [Id] [tinyint] IDENTITY(1,1) NOT NULL,
    [DateReporting] [datetime] NOT NULL,
    [TableName] [varchar](50) NOT NULL,
    [RowCount] [int] NOT NULL,
    [Reserved] [int] NOT NULL,
    [Data] [int] NOT NULL,
    [IndexSize] [int] NOT NULL,
    [Unused] [int] NOT NULL,
 CONSTRAINT [PK_DbStatistics] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Maybe somebody will find it usefull, Maybe Im missing something by saving those data...

http://nazermohamed.blogspot.co.uk/2012/03/quick-dump-of-all-table-sizes-of.html

set nocount on

declare  @objname sysname
   ,@objtype sysname
declare  @spaceused table (objtype sysname null, name sysname primary key, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

select  top 1 @objname = '[' + s.name + '].[' + o.name + ']'
   ,@objtype = o.type_desc
from  sys.objects o
join  sys.schemas s on o.schema_id = s.schema_id
order by 1

while  @@ROWCOUNT > 0
begin

 if @objtype not in ('SQL_STORED_PROCEDURE','SQL_INLINE_TABLE_VALUED_FUNCTION','PRIMARY_KEY_CONSTRAINT','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','UNIQUE_CONSTRAINT')
 begin
  insert @spaceused (name, rows, reserved, data, index_size, unused)
  exec sp_spaceused @objname
  update @spaceused set objtype = @objtype, name = @objname where objtype is null
 end else begin
  insert @spaceused (name, objtype)
  select @objname, @objtype
 end
  
 select  top 1 @objname = '[' + s.name + '].[' + o.name + ']'
    ,@objtype = o.type_desc
 from  sys.objects o
 join  sys.schemas s on o.schema_id = s.schema_id
 where  '[' + s.name + '].[' + o.name + ']' > @objname
 order by 1

end

update @spaceused
set  reserved = REPLACE(reserved, ' KB', '')
  ,data  = REPLACE(data, ' KB', '')
  ,index_size = REPLACE(index_size, ' KB', '')
  ,unused  = REPLACE(unused, ' KB', '')

select *
from @spaceused

We can get this result like this too.

DECLARE @tables TABLE(TabID INT IDENTITY(1,1)
      ,TableName VARCHAR(100)
      ,SchamaName VARCHAR(20)
      )

DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      )     

DECLARE @TabCount INT
DECLARE @TableName VARCHAR(100)
DECLARE @Start INT =1
INSERT INTO @tables
 SELECT st.name,ss.name FROM sys.tables st join sys.schemas ss
   ON(st.schema_id = ss.schema_id)
SET @TabCount = @@ROWCOUNT

 WHILE @Start < @TabCount
 BEGIN
 SELECT @TableName = TableName FROM @tables
  WHERE TabID = @Start
INSERT INTO @SpaceUsed
  EXEC sp_spaceused @TableName
 SET @Start = @Start + 1
 END 
 
 
 SELECT su.*,t.SchamaName FROM  @SpaceUsed su JOIN @tables t
    ON su.TableName = t.TableName
 
 
 

OR Simply use this..

DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      ) 
DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @command1=@str

Oops! Use this..

 DECLARE @SpaceUsed TABLE( TableName VARCHAR(100)
      ,No_Of_Rows BIGINT
      ,ReservedSpace VARCHAR(15)
      ,DataSpace VARCHAR(15)
      ,Index_Size VARCHAR(15)
      ,UnUsed_Space VARCHAR(15)
      ) 
DECLARE @str VARCHAR(500)
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @command1=@str
 
 SELECT * FROM @SpaceUsed

Thanks very much for the script!I'm a dummy in SQL and the script is very useful!

Now i can find out which table is using the most space with ease!