create table #tmplog (dbname sysname null, log_size float null, log_space_used float null, status int)
INSERT INTO #tmplog EXEC ('DBCC SQLPERF(LOGSPACE)')
SELECT
dtb.name AS [Name],
(select count(*) from master.dbo.sysprocesses p where dtb.dbid=p.dbid) AS [ActiveConnections],
dtb.cmptlevel AS [CompatibilityLevel],
dtb.crdate AS [CreateDate],
CAST(NULL AS float) AS [DataSpaceUsage],
CAST(0 AS bit) AS [DboLogin],
(select top 1 fg.groupname from dbo.sysfilegroups as fg where fg.status & 0x10 <> 0 ) AS [DefaultFileGroup],
'' AS [DefaultSchema],
CAST(dtb.dbid AS int) AS [ID],
CAST(NULL AS float) AS [IndexSpaceUsage],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible],
CAST(0 AS bit) AS [IsDbAccessAdmin],
CAST(0 AS bit) AS [IsDbBackupOperator],
CAST(0 AS bit) AS [IsDbDatareader],
CAST(0 AS bit) AS [IsDbDatawriter],
CAST(0 AS bit) AS [IsDbDdlAdmin],
CAST(0 AS bit) AS [IsDbDenyDatareader],
CAST(0 AS bit) AS [IsDbDenyDatawriter],
CAST(0 AS bit) AS [IsDbOwner],
CAST(0 AS bit) AS [IsDbSecurityAdmin],
CAST(DATABASEPROPERTYEX(dtb.name, 'IsFulltextEnabled') AS bit) AS [IsFullTextEnabled],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit) AS [IsSystemObject],
(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name = dtb.name) AS [LastBackupDate],
(select max(backup_finish_date) from msdb..backupset where type = 'L' and database_name = dtb.name) AS [LastLogBackupDate],
suser_sname(dtb.sid) AS [Owner],
rtrim(dtb.filename) AS [PrimaryFilePath],
dtb.category AS [ReplicationOptions],
CAST(NULL AS float) AS [Size],
t.log_size*(100-t.log_space_used)*10.24 AS [SpaceAvailable],
case
-- if all these are false then we are in the Normal state
-- except some return NULL if it's AutoClosed
when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and
(DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and
(DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and
DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and
DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and
DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and
(DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and
DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and
DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128
when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)
else 0
end |
case
when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256
else 0
end
AS [Status],
'' AS [UserName],
CAST(CHARINDEX(N'_CS_', CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS nvarchar(255))) AS bit) AS [CaseSensitive],
CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS sysname) AS [Collation],
CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable],
CAST(DATABASEPROPERTYEX(dtb.name, 'Version') AS int) AS [Version],
dtb.name AS [DatabaseName],
dtb.name AS [DatabaseName2]
FROM
master.dbo.sysdatabases AS dtb
LEFT OUTER JOIN #tmplog t ON t.dbname = dtb.name
WHERE
(dtb.name=N'pubs')
drop table #tmplog
