SQL Server’da Aktif Veritabanlarına Ait Bilgileri Detaylı Görüntülemek

Herkese merhaba,

Bu yazıda SQL Server’da aktif veritabanlarına ait bilgileri detaylı görüntülemek ile ilgili bilgi vermeye çalışacağım.

SQL Server’da bazı durumlarda aktif veritabanlarına ait bilgileri detaylı görüntülemek isteyebilirsiniz.

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

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    DROP TABLE #TempTable;
CREATE TABLE #TempTable
(
    ServerName VARCHAR(128)
        DEFAULT @@servername,
    DBName VARCHAR(128)
        DEFAULT DB_NAME(),
    DBOwner VARCHAR(128),
    CreateDate DATETIME2,
    RecoveryModel VARCHAR(12),
    StateDesc VARCHAR(60),
    CompatibilityLevel INT,
    DataFileSizeMB INT,
    LogFileSizeMB INT,
    DataUsageMB INT,
    IndexUsageMB INT,
    SizeMB DECIMAL(17, 2),
    Collation VARCHAR(60),
    UserCount INT,
    RoleCount INT,
    TableCount INT,
    SPCount INT,
    UDFCount INT,
    ViewCount INT,
    DMLTriggerCount INT,
    IsCaseSensitive BIT,
    IsTrustWorthy BIT,
    LastFullBackupDate DATETIME2,
    LastDiffBackupDate DATETIME2,
    LastLogBackupDate DATETIME2
);

INSERT INTO #TempTable
(
    DBName,
    DBOwner,
    CreateDate,
    RecoveryModel,
    StateDesc,
    CompatibilityLevel,
    IsCaseSensitive,
    IsTrustWorthy,
    Collation,
    LastFullBackupDate,
    LastDiffBackupDate,
    LastLogBackupDate
)
SELECT name,
       SUSER_SNAME(owner_sid),
       create_date,
       recovery_model_desc,
       state_desc,
       compatibility_level,
       IsCaseSensitive = CAST(CHARINDEX(N'_CS_', collation_name) AS BIT),
       is_trustworthy_on,
       collation_name,
       t.LastFullBackup,
       t.LastDiffBackup,
       t.LastLogBackup
FROM master.sys.databases db
    OUTER APPLY
(
    SELECT MAX(   CASE
                      WHEN b.type = 'D' THEN
                          b.backup_finish_date
                  END
              ) AS LastFullBackup,
           MAX(   CASE
                      WHEN b.type = 'I' THEN
                          b.backup_finish_date
                  END
              ) AS LastDiffBackup,
           MAX(   CASE
                      WHEN b.type = 'L' THEN
                          b.backup_finish_date
                  END
              ) AS LastLogBackup
    FROM msdb.dbo.backupset b
    WHERE b.database_name = db.name
) t;

EXEC master.dbo.sp_MSforeachdb 'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize 
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #TempTable t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128 
   FROM sys.database_files df 
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply 
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply 
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
';
SELECT *
FROM #TempTable;

Yukarıdaki kodu oluşturup çalıştırınca aşağıdakine benzer bir sonuç göreceksiniz.

Görüldüğü üzere aktif veritabanlarına ait bilgileri detaylı görüntülemiş olduk.

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