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