sqlserver的日常检查脚本归结

WHEN s.groupid = 0 THEN 'log'                      

END) AS 'fileType'                      

, d.dbid as 'DBID'                      

, d.name AS 'DBName'                      

, s.name AS 'LogicalFileName'                      

, s.filename AS 'PhysicalFileName'                      

, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                      

, d.cmptlevel as 'CompatibilityLevel'                      

, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                      

, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                   　

--, d.is_published as 'Publisher'                      

--, d.is_subscribed as 'Subscriber'                      

--, d.is_distributor as 'Distributor' 

(SELECT 'is_replication' =                      

CASE                      

WHEN d.category = 1 THEN 'Published'                      

WHEN d.category = 2 THEN 'subscribed'                      

WHEN d.category = 4 THEN 'Merge published'

WHEN d.category = 8 THEN 'merge subscribed'

Else 'NO replication'

END) AS 'Is_replication'                      

, m.mirroring_state as 'MirroringState'                      

--INTO master.[dbo].[databasedetails]                      

FROM                      

sys.sysdatabases d INNER JOIN sys.sysaltfiles s