SQL Server - Table Size and Index Size With Number Count By Schema

When we talk about database growth report then one procedure comes in mind "sp_spaceused". This procedure return space used table wise.


Here I have done some modification to achieve report schema wise with table and index count. Size considered in MB for table and index.


Following are the T-SQL - You can just copy and paste and get resultset:


------------------------------------------------------


SET NOCOUNT ON;


DECLARE @TableName VARCHAR(200)
declare @id int -- The object id that takes up space
,@type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages  bigint
,@usedpages  bigint
,@rowCount bigint
,@schemaId int
,@objname nvarchar(776)
,@indexCount int


-- Insert statements for procedure here
DECLARE tableCursor CURSOR FOR
SELECT sys.schemas.[name]+'.'+sys.objects.[name]
FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id
WHERE type='U' AND is_ms_shipped=0 ORDER BY sys.schemas.[name] -- WHERE is_ms_shipped is Microsoft generated objects


FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(200),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize float,
indexSize float,
unusedSize varchar(50),
schemaName varchar(200),
indexCount int
)


--Open the cursor
OPEN tableCursor


--Get the first Record from the cursor
FETCH NEXT FROM tableCursor INTO @TableName


--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
------------------------------------------------------
Set @objname = @TableName
if @objname IS NOT NULL
begin


select @dbname = parsename(@objname, 3)


if @dbname is null
select @dbname = db_name()


/*
**  Try to find the object.
*/
SELECT @id = object_id, @type = type,@schemaId=schema_id FROM sys.objects WHERE object_id = object_id(@objname)


-- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue


end


set nocount on


/*
** Now calculate the summary data. 
*  Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT 
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = 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
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;


/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0 
BEGIN
/*
**  Now calculate the summary data. Row counts in these internal tables don't 
**  contribute towards row count of original table.
*/
SELECT 
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END


-- Count indexes per table
select @indexCount = COUNT(index_id) from sys.indexes where object_id=@id

--Insert the results of the sp_spaceused query to the temp table
INSERT #TempTable(tableName,numberofRows,reservedSize,dataSize,indexSize,unusedSize,schemaName,indexCount)
SELECT 
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0)),
data = LTRIM (STR (@pages * 8, 15, 0)),
index_size = ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0)),
schemaName = SCHEMA_NAME(@schemaId),
indexCount = ISNULL(@indexCount,0)
--EXEC sp_DatabaseGrowth @TableName
------------------------------------------------------
--Get the next Record
FETCH NEXT FROM tableCursor INTO @TableName
END


--Close/Deallocate the cursor
CLOSE tableCursor
DEALLOCATE tableCursor


--Select all records so we can use the reults
--SELECT * FROM #TempTable
select @dbname as [Database Name], schemaname as [Schema Name],COUNT(dataSize) as [Number Table], SUM(indexCount) as [Number Index], 
cast(SUM(isnull(dataSize,0))/1024 as decimal(10,3)) as [Table Size(MB)], cast(SUM(ISNULL(indexSize,0))/1024 as decimal(10,3)) as [Index Size(MB)] 
from #TempTable group by schemaName


DROP TABLE #TempTable
------------------------------------------------------


Thanks.