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
|