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
Advertisements

One Response to “Table statistics in SQL Server 2005/2008/2000 like Table size, total row, index size etc.”

  1. Jeff Moden Says:

    sp_msforeachtable doesn’t avoid the Cursor. Look at the code for it. It’s a MONSTER cursor!

    Of course, not all usage of a Cursor is unwarranted.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: