Calculate the table size and number of rows

At some point you might need to know, how many tables you have in database, number of rows in each table, size of table and indices. Following is a simple SQL Script which can provide you a snapshot of all such details.

---

 Declare @TabName Varchar(100)

Create table #TempTable (Name varchar(100), Rows int,Reserved varchar(25),Data varchar(25),Index_size varchar(25),Unused varchar(25))

 

Declare TableList cursor LOCAL FAST_FORWARD FOR     

SELECT Name FROM sysobjects

WHERE xtype='U'

ORDER by Name

OPEN TableList     

     

FETCH NEXT FROM TableList INTO @TabName         

WHILE @@fetch_status = 0        

 BEGIN   

        INSERT INTO #TempTable Exec sp_spaceused @TabName

        FETCH NEXT FROM TableList INTO @TabName   

 End

CLOSE TableList
DEALLOCATE TableList

SELECT * FROM #TempTable