Vishwamohan

Welcome to Vishwa's blog - Technology, Spirituality and More...

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

Comments (2) -

  • shubhavani

    6/2/2007 7:39:52 AM | Reply

    useful procedure. Thanks.

  • Michal Ditto

    9/4/2010 9:20:56 AM | Reply

    I've long read any weblogs anymore simply because as well a lot of websites had been either uninteresting or not recent. Or at worst, each. Here it can be unique, the website is thrilling and informative. Thank god you will discover still such sites for the net. Then yet again it can be really worth browsing.

Loading