2777.aspx

How to display number of records, data and index space in Microsoft SQL Server

I got the great query below from Michele today. It shows all the information you ever wanted to know about the tables in your SQL Server, in particular; the number of rows as well as the size of the data and index.

DECLARE @Tab sysname

 

DECLARE tables_Cursor CURSOR FOR

SELECT name

from sysobjects

WHERE xType='u'

 

OPEN tables_Cursor

 

      FETCH NEXT FROM tables_Cursor INTO @tab

 

WHILE @@FETCH_STATUS = 0

BEGIN

      select @tab
      -- Show space used by table

      exec ('sp_mstablespace ''' + @tab + '''')

      -- Show generic table info

      exec ('sp_help ''' + @tab + '''')

      FETCH NEXT FROM tables_Cursor INTO @tab

END

 

CLOSE tables_Cursor

DEALLOCATE tables_Cursor