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

Saturday, May 30, 2009 |

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

0 comments: