SQL Server’da Veritabanındaki En Büyük Tabloları ve Index’leri Listelemek

Herkese merhaba. Bu yazıda SQL Server’da veritabanındaki en büyük tabloları ve index’leri listelemek ile ilgili bilgi vermeye çalışacağım.

SQL Server’da bazı durumlarda veritabanındaki en büyük tabloları ve Index’leri listelemek isteyebilirsiniz.

Aşağıdaki kodu kullanarak sizler de bu işlemi rahatlıkla yapabilirsiniz.

En Büyük Tabloları Listelemek

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

En Büyük Index’leri Listelemek

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

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.