MSSQL Server Veritabanı Altında Heap Table Bulma Komutu

Bu makalede veritabanı altında bulunan heap tabloları bulan script’i paylaşmış olacağız. Heap tablo, SQL Server’da bir tablonun indeks içermeyen, sırasız bir yapıda olduğunu ifade eder. Yani, heap tablolarında veriler herhangi bir düzen veya sıralama olmaksızın doğrudan yerleştirilir. Diğer bir deyişle, bir heap tablosunda veriler fiziksel olarak belirli bir sıralamaya göre depolanmaz. Tablo oluşturulurken primary key oluşturmazsak tablomuz heap yapıda oluşur. Bu yapı verilerin tabloya rastgele yazılmasına sebep olmaktadır. Performans anlamında sadece insert işlemlerinde primary key ile oluşturulan tablolara göre hızlıdır. Çünkü veriler rastgele yazılır. Herhangi bir kuraldan geçmez. Tablo üzerinde arama işlemlerinde tablo tamamen taranacağı için performans anlamında kötü bir tablo oluşturma biçimidir.

Aşağıdaki komut ile veritabanı altında heap tabloları ve tablo oluşturma tarihlerini görmekteyiz.

SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON  i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'
ORDER BY o.name
GO

Sadece veritabanı altında bulunan tabloları görmek istersek aşağıdaki komut kullanılır.

SELECT SCH.name + '.' + TBL.name AS TableName
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id
                AND IDX.type = 0
ORDER BY TableName

Aşağıdaki komut ile instance altında bulunan tüm veritabanları altında bulunan Heap tablo sayısını vermektedir. Yukarıdaki resimde AdventureWorks veritabanı altında 3 heap tablomuz vardı. Aşağıdaki komutta aynı sonucu vermektedir.

EXEC sp_MSforeachdb 'USE [?]
SELECT  ''?'' AS DatabaseName,
COUNT(o.name) AS Count
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP'' '

Veritabanı altında bulunan heap tablo sayısı değilde instance altında bulunan tüm veritabanları için sonuç döndürmek istersek aşağıdaki sonuç kullanılmaktadır.

EXEC sp_MSforeachdb '
USE [?]
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'') -- Sistem DB''lerini atla
BEGIN
SELECT  ''?'' AS DatabaseName,
o.name,
i.type_desc,
o.type_desc,
o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON  i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP''
END '

Aşağıdaki komut ile tüm instance altında bulunan veritabanlarının heap olan tablolarında son kullanma tarihini vermektedir.

EXEC sp_MSforeachdb '
USE [?]
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'') -- Sistem DB''lerini atla
BEGIN
    SELECT 
        ''?'' AS Veritabanı,
        SCHEMA_NAME(o.schema_id) AS Şema,
        o.name AS TabloAdı,
        o.type_desc AS NesneTürü,
        o.create_date AS OluşturmaTarihi,
        us.last_user_scan AS SonTarama,
        us.last_user_lookup AS SonLookup,
        us.last_user_update AS SonGüncelleme,
        CASE
            WHEN us.last_user_scan IS NULL AND us.last_user_lookup IS NULL AND us.last_user_update IS NULL THEN ''Hiç kullanılmamış''
            ELSE CONVERT(VARCHAR(50), 
                CASE 
                    WHEN us.last_user_scan > us.last_user_lookup AND us.last_user_scan > us.last_user_update THEN us.last_user_scan
                    WHEN us.last_user_lookup > us.last_user_update THEN us.last_user_lookup
                    ELSE us.last_user_update
                END, 120)
        END AS SonKullanımTarihi
    FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    LEFT JOIN sys.dm_db_index_usage_stats us ON us.object_id = i.object_id 
        AND us.index_id = i.index_id 
        AND us.database_id = DB_ID()
    WHERE o.type_desc = ''USER_TABLE''
        AND i.type_desc = ''HEAP''
        AND o.is_ms_shipped = 0
    ORDER BY SonKullanımTarihi DESC
END
'

Yukarıdaki komut ile hem kullanılan heap tablolar hemde kullanılmayan heap tabloları gösterilmektedir. Sayfamızda kullanılmayan tablolar makalesinden heap olup hiç kullanılmayan tablolarda görülebilir. Aşağıdaki komut ile de heap olup kullanılan tablolar görülmektedir.

EXEC sp_MSforeachdb '
USE [?]
IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'') -- Sistem DB''lerini atla
BEGIN
    SELECT 
        ''?'' AS Veritabanı,
        SCHEMA_NAME(o.schema_id) AS Şema,
        o.name AS TabloAdı,
        o.type_desc AS NesneTürü,
        o.create_date AS OluşturmaTarihi,
        us.last_user_scan AS SonTarama,
        us.last_user_lookup AS SonLookup,
        us.last_user_update AS SonGüncelleme,
        CONVERT(VARCHAR(50), 
            CASE 
                WHEN us.last_user_scan > us.last_user_lookup AND us.last_user_scan > us.last_user_update THEN us.last_user_scan
                WHEN us.last_user_lookup > us.last_user_update THEN us.last_user_lookup
                ELSE us.last_user_update
            END, 120) AS SonKullanımTarihi
    FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    INNER JOIN sys.dm_db_index_usage_stats us ON us.object_id = i.object_id 
        AND us.index_id = i.index_id 
        AND us.database_id = DB_ID()
    WHERE o.type_desc = ''USER_TABLE''
        AND i.type_desc = ''HEAP''
        AND o.is_ms_shipped = 0
        AND (
            us.last_user_scan IS NOT NULL OR 
            us.last_user_lookup IS NOT NULL OR 
            us.last_user_update IS NOT NULL
        )
    ORDER BY SonKullanımTarihi DESC
END
'

Bu makalede veritabanı altında bulunan heap tabloları görmüş olduk. Başka makalede görüşmek dileğiyle..

*Göklerde ve yerde ne varsa hepsi Allah’ındır. Allah’ın ilmi ve kudreti her şeyi kuşatmıştır. Nisa-126*