SQL Server'da Veri Tabanı Envanteri Alınması
Aşağıdaki T-SQL query’si ile SQL Server üzerindeki aktif veritabanlarının bilgilerini görüntüleyebilirsiniz.
- Database adı
- DBOwner
- Oluşturma Tarihi
- Recovery Mode
- Durum Açıklaması
- Compatibility Level
- Data File Boyutu
- Log Dosya boyutu
- Data Kullanım Boyutu
- Index Kullanım Boyutu
- Toplam Boyutu
- Collation
- User Count
- Role Count
- Table Count
- SP Count
- View Count
- DML Trigger Count
- TrustWorthy
- En son alınan Full Yedekleme tarih bilgisi
- En son alınan Differantial Yedekleme tarih bilgisi
- En son alınan Log Yedekleme tarih bilgisi
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | if object_id('tempdb..#t', 'U') is not nulldrop table #t;create table #t( 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 #t(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.LastLogBackupfrom master.sys.databases dbouter apply( SELECTMAX(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 LastLogBackupFROM 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.RCfrom #t 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 #t |
Yorumlar