Table Size and Rows

declare @t table (

name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)

)

declare @name nvarchar(100)

declare tt cursor for

Select  QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Name] from 

sys.objects AS sOBJ

 INNER JOIN sys.partitions AS sPTN

 ON sOBJ.object_id = sPTN.object_id WHERE

 sOBJ.type = 'U'AND sOBJ.is_ms_shipped = 0x0

 AND index_id < 2 -- 0:Heap, 1:Clustered

 GROUP BY sOBJ.schema_id, sOBJ.name

---Select name from sys.tables sOBJ

open tt

fetch next from tt into @name

while @@FETCH_STATUS = 0

begin

  insert into @t

  exec sp_spaceused @name

  fetch next from tt into @name

end

close tt

deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size

from (select name,

[rows],

cast (LEFT(data, LEN(data)-3) as int) data,

cast (LEFT(index_size, LEN(index_size)-3) as int) [index]

 from @t

) x

order by 3 desc, 1


OR

SELECT SCH.name AS SchemaName

      ,OBJ.name AS ObjName

      ,OBJ.type_desc AS ObjType

      ,INDX.name AS IndexName

      ,INDX.type_desc AS IndexType

      ,PART.partition_number AS PartitionNumber

      ,PART.rows AS PartitionRows

      ,STAT.row_count AS StatRowCount

      ,STAT.used_page_count * 8 AS UsedSizeKB

      ,STAT.reserved_page_count * 8 AS RevervedSizeKB

FROM sys.partitions AS PART

     INNER JOIN sys.dm_db_partition_stats AS STAT

         ON PART.partition_id = STAT.partition_id

            AND PART.partition_number = STAT.partition_number

     INNER JOIN sys.objects AS OBJ

         ON STAT.object_id = OBJ.object_id

     INNER JOIN sys.schemas AS SCH

         ON OBJ.schema_id = SCH.schema_id

     INNER JOIN sys.indexes AS INDX

         ON STAT.object_id = INDX.object_id

            AND STAT.index_id = INDX.index_id

ORDER BY SCH.name

        ,OBJ.name

        ,INDX.name

        ,PART.partition_number