SQL Server views the size and space of all tables

Create   Table # Data (name Varchar ( 100 ), Row Varchar ( 100 ), Reserved Varchar ( 100 ), Data Varchar ( 100 ), Index_size Varchar ( 100 ), UnusedVarchar ( 100  ))  Declare   @ Name   Varchar ( 100  )  Declare Cur Cursor    For       Select Name From Sysobjects Where Xtype = '  U  '   Order   By  Name  Open  Cur  Fetch   Next   From Cur Into   @ Name   While   @ Fetch_status  =  0  Begin       Insert   Into  # Data  Exec Sp_spaceused @ Name       Print   @ Name        Fetch   Next   From Cur Into   @ Name   End   Close  Cur Deallocate  Cur  Create   Table # Datanew (name Varchar ( 100 ), Row Int , Reserved Int , Data Int , Index_size Int , Unused Int  )  Insert   Into # Datanew  Select Name, Convert ( Int , Row) As Row, Convert ( Int , Replace (Reserved, '  KB  ' , '' )) As Reserved, Convert (Int , Replace (Data, '  KB  ' , '' )) As  Data,  Convert ( Int , Replace (Index_size, '  KB  ' , '' ))As Index_size, Convert ( Int , Replace (Unused, '  KB  ' , '' )) As Unused From  # Data  Select   *   From # Datanew Order  By Data Desc     

-- Main principle:
Exec sp_spaceused 'table name' -- get the table space occupied
Exec sp_spaceused ''-- all spaces in the data queue

There is also a simple way

 Select  A. Name, B. Rows  From Sysobjects As A Inner   Join  Sysindexes  As B On A. ID = B. ID  Where (A. Type =   '  U  ' ) And (B. indid In ( 0 , 1  ))  Order   By B. Rows Desc