Get Index Size and Counts

DROP PROCEDURE dbo.usp_GetTableSizes_2000

Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2000

AS

/**********************************************************

Purpose: To retrieve table and index sizes for all tables

in a DB. Index and Statistics (ie '_wa_%') counts and

index statistics update date are also included

Author: ChillyDBA

History: 2001 - Initial Release

***********************************************************/

CREATE TABLE #TableSizes

(

name SYSNAME,

rows INT,

reserved VARCHAR(30),

data VARCHAR(30),

index_size VARCHAR(30),

unused VARCHAR(30)

)

CREATE TABLE #TableIndexes

(

name SYSNAME,

total_indexes INT,

total_clustered INT,

total_nonclustered INT,

total_statistics INT,

stats_last_updated DATETIME

)

DECLARE

@SnapshotDate DATETIME, -- in case this information is to be persisted for trend analysis

@TableName SYSNAME,

@Cmd VARCHAR(1000)

SELECT @SnapshotDate = GETDATE()

SELECT @TableName = MIN(name)

FROM sysobjects

WHERE TYPE = 'u'

AND name NOT LIKE 'sys%'

WHILE @TableName IS NOT NULL

BEGIN

SELECT @Cmd = 'sp_spaceused ' + @TableName

INSERT #TableSizes

EXEC (@Cmd)

INSERT #TableIndexes (name, total_indexes, total_nonclustered, total_clustered, total_statistics, stats_last_updated)

SELECT @TableName,

-- get # indexes that are not system stats

(SELECT COUNT(*)

FROM sysindexes

WHERE id = OBJECT_ID(@TableName)

AND name NOT LIKE '_wa_%'

AND indid > 0),

-- get # nonclustered indexes that are not system stats

(SELECT COUNT(*)

FROM sysindexes

WHERE id = OBJECT_ID(@TableName)

AND name NOT LIKE '_wa_%'

AND indid > 1),

-- get # clustered indexes that are not system stats

(SELECT COUNT(*)

FROM sysindexes

WHERE id = OBJECT_ID(@TableName)

AND name NOT LIKE '_wa_%'

AND indid = 1),

-- get # system stats

(SELECT COUNT(*)

FROM sysindexes

WHERE id = OBJECT_ID(@TableName)

AND name LIKE '_wa_%'

AND indid > 0),

-- get date that index stats were last updated

(SELECT MAX(STATS_DATE(ID, indid))

FROM sysindexes

WHERE id = OBJECT_ID(@TableName))

SELECT @TableName = MIN(name)

FROM sysobjects

WHERE TYPE = 'u'

AND name > @TableName

AND name NOT LIKE 'sys%'

AND uid = 1

END

--insert dba_table_sizes

SELECT

DB_NAME() AS DatabaseName,

ts.name AS TableName,

-- remove the kb suffix so that these figures can be manipulated easily in excel or any other query

REPLACE(rows, 'kb','') AS #Rows,

REPLACE(reserved, 'kb','') AS KB_Reserved,

REPLACE(data, 'kb','') AS KB_Data,

REPLACE(index_size, 'kb','') AS KB_Index,

REPLACE(unused, 'kb','') AS KB_Unused,

ti.total_indexes AS #TotalIndexes,

ti.total_nonclustered AS #NonClusteredIndexes,

ti.total_clustered AS #ClusteredIndexes,

ti.total_statistics AS #Statistics,

ti.stats_last_updated AS StatsLastUpdatedDate,

DATEDIFF( dd, ti.stats_last_updated, GETDATE()) AS StatsAgeInDays,

@SnapshotDate AS SnapshotDate

FROM #TableSizes ts

INNER JOIN #TableIndexes ti

ON ts.name = ti.name

--order by cast(replace(reserved, 'kb','')as int) desc

ORDER BY ts.name

DROP TABLE #TableSizes

DROP TABLE #TableIndexes

DROP PROCEDURE dbo.usp_GetTableSizes_2005

Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2005

AS

/**********************************************************

Purpose: To retrieve table and index sizes for all tables

in a DB. Index and Statistics (ie '_wa_%') counts and

index statistics update date are also included

Author: Jeffrey Yao

History: 27 Sep 2010 - Initial Release

19 Jun 2012 - ChillyDBA - Augmented with # coumns to match the

output with the usp_GetTableSizes_2000 SP

***********************************************************/

DECLARE

@SnapshotDate DATETIME -- in case this information is to be persisted for trend analysis

SELECT @SnapshotDate = GETDATE()

SELECT

DB_Name() AS DatabaseName,

object_schema_name(OBJECT_ID) + '.' + OBJECT_NAME(OBJECT_ID) AS TableName,

SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) AS #Rows,

8*SUM(reserved_page_count) AS KB_Reserved,

8*SUM

( CASE

WHEN index_id<2 THEN in_row_data_page_count

+ lob_used_page_count

+ row_overflow_used_page_count

ELSE lob_used_page_count

+ row_overflow_used_page_count

END

) AS KB_Data,

8*

(

SUM(used_page_count) - SUM

( CASE

WHEN index_id<2 THEN in_row_data_page_count

+ lob_used_page_count

+ row_overflow_used_page_count

ELSE lob_used_page_count

+ row_overflow_used_page_count

END

)

) AS KB_Index,

8*SUM(reserved_page_count-used_page_count) AS KB_Unused,

(

SELECT COUNT(*)

FROM sys.dm_db_partition_stats ps2 (NOLOCK)

WHERE ps.object_id = ps2.object_id

AND OBJECT_NAME(ps2.object_ID) NOT LIKE '_wa_%'

AND ps2.index_id > 0

) AS #TotalIndexes,

(

SELECT COUNT(*)

FROM sys.dm_db_partition_stats ps3 (NOLOCK)

WHERE ps.object_id = ps3.object_id

AND OBJECT_NAME(ps3.object_ID) NOT LIKE '_wa_%'

AND ps3.index_id > 1

) AS #TotalNonClusteredIndexes,

(

SELECT COUNT(*)

FROM sys.dm_db_partition_stats ps4 (NOLOCK)

WHERE ps.object_id = ps4.object_id

AND OBJECT_NAME(ps4.object_ID) NOT LIKE '_wa_%'

AND ps4.index_id = 1

) AS #TotalClusteredIndexes,

(

SELECT COUNT(*)

FROM sysindexes si (NOLOCK)

WHERE ps.object_id = si.id

AND si.name LIKE '_wa_%'

AND si.indid > 0

) AS #Statistics,

(

SELECT MAX(STATS_DATE(object_id, index_id))

FROM sys.dm_db_partition_stats ps6 (NOLOCK)

WHERE ps.object_id = ps6.object_id

) AS StatsLastUpdatedDate,

DATEDIFF( dd, (

SELECT MAX(STATS_DATE(object_id, index_id))

FROM sys.dm_db_partition_stats ps6 (NOLOCK)

WHERE ps.object_id = ps6.object_id

), GETDATE()) AS StatsAgeInDays,

@SnapshotDate AS SnapshotDate

FROM sys.dm_db_partition_stats ps (NOLOCK)

WHERE OBJECT_ID > 1024

GROUP BY OBJECT_ID

ORDER BY TableName