Listing Largest Tables and Indexes in Database in SQL Server

Hello everyone. In this article, I will try to give information about listing the largest tables and indexes in the database in SQL Server.

In SQL Server, in some cases you may want to list the largest tables and Indexes in the database.

You can easily do this using the code below.

Listing the Largest Tables

SELECT TOP 100
	s.[name] AS [schema]
   ,t.[name] AS [table_name]
   ,p.[rows] AS [row_count]
   ,CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb]
   ,CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb]
   ,CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM sys.tables t
JOIN sys.indexes i
	ON t.[object_id] = i.[object_id]
JOIN sys.partitions p
	ON i.[object_id] = p.[object_id]
		AND i.index_id = p.index_id
JOIN sys.allocation_units a
	ON p.[partition_id] = a.container_id
LEFT JOIN sys.schemas s
	ON t.[schema_id] = s.[schema_id]
WHERE t.is_ms_shipped = 0
AND i.[object_id] > 255
GROUP BY t.[name]
		,s.[name]
		,p.[rows]
ORDER BY [size_mb] DESC

Listing the Biggest Indexes

SELECT TOP(100)
    s.[name] AS [schema],
    t.[name] AS [table_name],
    i.[name] AS [index_name],
    i.[type_desc],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name],
    i.[name],
    i.[type_desc],
    p.[rows]
ORDER BY 
    [size_mb] DESC

Good luck to everyone in business and life.