Tuesday, May 30, 2006

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

No comments:

Post a Comment