Table statistics in SQL Server 2005/2008/2000 like Table size, total row, index size etc.

We may often need to see total row in each table, data size, index size, used and unused space in table etc. Well, in this case SP_SpaceUsed stored procedure could be used but it shows you states of only one table, what if we wants this type of statistics for all use tables in entire database? In this case, undocumented stored procedure “SP_MsForEachTable” will come to our help. Let us see it practically.

--create SP which will display states of all tables

Create proc tableState

AS

--create temporary table

--which will store all states

CREATE TABLE #TableState (

       tableName sysname ,

       rowCounts INT,

       reservedSize VARCHAR(50),

       dataSize VARCHAR(50),

       indexSize VARCHAR(50),

       unusedSize VARCHAR(50))

SET NOCOUNT ON

--insert result of SP_SpaceUsed in temp table

--if you go for CURSOR or LOOP you don't need to use

--sp_msforeachtable stored procedure

--but to avoid CURSOR,sp_msforeachtable could be good alternative

INSERT #TableState

  EXEC sp_msforeachtable 'sp_spaceused ''?'''

--display data of temp table

SELECT * FROM #TableState order by tableName

--drop temp table explicitly!!

DROP TABLE #TableState

GO

--run above SP

exec tableState

 

 

Happy Programming!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com