Get SQL Server Database details using T-SQL

Here is a T-SQL script which gives you the details of all the databases in an SQL Server Instance.
This will be very useful when you are gathering SQL Server information from multiple servers

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails
CREATE TABLE #DatabaseDetails (
  DatabaseID int
, DatabaseName varchar(256)
, CreateDate datetime
, Collation varchar(256)
, ComparisonStyle int
, IsAnsiNullDefault bit
, IsAnsiNullsEnabled bit
, IsAnsiPaddingEnabled bit
, IsAnsiWarningsEnabled bit
, IsArithmeticAbortEnabled bit
, IsAutoClose bit
, IsAutoCreateStatistics bit
, IsAutoShrink bit
, IsAutoUpdateStatistics bit
, IsCloseCursorsOnCommitEnabled bit
, IsFulltextEnabled bit
, [IsInStandBy] bit
, IsLocalCursorsDefault bit
, IsMergePublished bit
, IsMergeSubscribed bit
, IsNullConcat bit
, IsNumericRoundAbortEnabled bit
, IsParameterizationForced bit
, [IsQuotedIdentifiersEnabled] bit
, IsPublished bit
, IsRecursiveTriggersEnabled bit
, IsSubscribed bit
, IsSyncWithBackup bit
, IsTornPageDetectionEnabled bit
, LCID int
, [Recovery] varchar(256)
, [SQLSortOrder] tinyint
, [Status] varchar(256)
, Updateability varchar(256)
, UserAccess varchar(256)
, [Version] int
, LastDatabaseBackup datetime
, LastIncremetalBackup datetime
, LastLogBackup datetime
, TotalLogSize bigint
, LogPercentUsed int
, [TotalDBSize_MB] bigint
, [cmptlevel] int
)

INSERT INTO #DatabaseDetails(
  DatabaseID
, [DatabaseName]
, [CreateDate]
, [Collation]
, [ComparisonStyle]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsAutoCreateStatistics]
, [IsAutoShrink]
, [IsAutoUpdateStatistics]
, [IsCloseCursorsOnCommitEnabled]
, [IsFulltextEnabled]
, [IsInStandBy]
, [IsLocalCursorsDefault]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsNullConcat]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsPublished]
, [IsRecursiveTriggersEnabled]
, [IsSubscribed]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
, [LCID]
, [Recovery]
, [SQLSortOrder]
, [Status]
, [Updateability]
, [UserAccess]
, [Version]
, [cmptlevel])
SELECT sd.dbid as 'DatabaseID'
 , sd.[name] as 'DatabaseName'
 , sd.crdate as 'CreateDate'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Collation') as varchar(256)) as 'Collation'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'ComparisonStyle') as varchar(256)) as 'ComparisonStyle'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullDefault') as bit) as 'IsAnsiNullDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiNullsEnabled') as bit) as 'IsAnsiNullsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiPaddingEnabled') as bit) as 'IsAnsiPaddingEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAnsiWarningsEnabled') as bit) as 'IsAnsiWarningsEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsArithmeticAbortEnabled') as bit) as 'IsArithmeticAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoClose') as bit) as 'IsAutoClose'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoCreateStatistics') as bit) as 'IsAutoCreateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoShrink') as bit) as 'IsAutoShrink'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsAutoUpdateStatistics') as bit) as 'IsAutoUpdateStatistics'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsCloseCursorsOnCommitEnabled') as bit) as 'IsCloseCursorsOnCommitEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsFulltextEnabled') as bit) as 'IsFulltextEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsInStandBy') as bit) as 'IsInStandBy'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsLocalCursorsDefault') as bit) as 'IsLocalCursorsDefault'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsMergePublished') as bit) as 'IsMergePublished'
 , CASE WHEN sd.category & 8 = 8 THEN 1 ELSE 0 end as 'IsMergeSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNullConcat') as bit) as 'IsNullConcat'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsNumericRoundAbortEnabled') as bit) as 'IsNumericRoundAbortEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsParameterizationForced') as bit) as 'IsParameterizationForced'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsQuotedIdentifiersEnabled') as bit) as 'IsQuotedIdentifiersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsPublished') as bit) as 'IsPublished'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsRecursiveTriggersEnabled') as bit) as 'IsRecursiveTriggersEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSubscribed') as bit) as 'IsSubscribed'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsSyncWithBackup') as bit) as 'IsSyncWithBackup'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'IsTornPageDetectionEnabled') as bit) as 'IsTornPageDetectionEnabled'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'LCID') as int) as 'LCID'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Recovery') as varchar(256)) as 'Recovery'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'SQLSortOrder') as tinyint) as 'SQLSortOrder'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Status') as varchar(256)) as 'Status'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Updateability') as varchar(256)) as 'Updateability'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'UserAccess') as varchar(256)) as 'UserAccess'
 , CAST(DATABASEPROPERTYEX(sd.[name], 'Version') as int) as 'Version'
 , cmptlevel
FROM master.dbo.sysdatabases sd 
ORDER BY sd.dbid



UPDATE dbd
SET   LastDatabaseBackup = fullbak.LastDatabaseBackup
 , LastIncremetalBackup = incbak.LastIncremetalBackup
 , LastLogBackup = logbak.LastLogBackup
FROM #DatabaseDetails dbd
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t1.backup_finish_date) as 'LastDatabaseBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t1  on t1.type = 'D' and t1.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) fullbak ON fullbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t2.backup_finish_date) as 'LastIncremetalBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t2  on t2.type = 'I' and t2.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) incbak on incbak.dbid = dbd.DatabaseID
left join (
 SELECT sd.dbid
   , sd.[name] as 'DatabaseName'
   , max(t3.backup_finish_date) as 'LastLogBackup'
 FROM master.dbo.sysdatabases sd 
 join msdb.dbo.backupset t3  on t3.type = 'L' and t3.database_name = sd.[name]
 GROUP BY sd.dbid, sd.[name]
) logbak on logbak.dbid = dbd.DatabaseID


IF OBJECT_ID('tempdb..#logspace') IS NOT NULL DROP TABLE #logspace
CREATE TABLE #logspace(
   DatabaseName varchar(256)
 , TotalLogSize decimal(20,4)
 , PercentUsed decimal(20,4)
 , [Status] varchar(50)
)
INSERT INTO #logspace
EXEC('DBCC sqlperf(logspace)')

UPDATE dbd
SET   TotalLogSize = ls.TotalLogSize
 , LogPercentUsed = Convert(int, ls.PercentUsed)
FROM #DatabaseDetails dbd
join #logspace ls ON dbd.DatabaseID = db_id(ls.DatabaseName)

EXEC master.dbo.sp_MSForEachDB 'update #DatabaseDetails
set TotalDBSize_MB = (select (sum([size]) * 8.0) / 1024.0 
FROM [?].[dbo].[sysfiles] )
where DatabaseID = db_id(''?'')'

SELECT DatabaseID
, DatabaseName
, [TotalDBSize_MB]
, TotalLogSize
, LogPercentUsed
, CreateDate
, [Status]
, LastDatabaseBackup
, LastIncremetalBackup
, LastLogBackup
, [Recovery]
, [Updateability]
, [UserAccess]
, [Collation]
, [ComparisonStyle]
, [LCID]
, [SQLSortOrder]
, [Version]
, [cmptlevel]
, [IsAutoUpdateStatistics]
, [IsAutoCreateStatistics]
, [IsInStandBy]
, [IsAutoShrink]
, [IsNullConcat]
, [IsFulltextEnabled]
, [IsPublished]
, [IsSubscribed]
, [IsMergePublished]
, [IsMergeSubscribed]
, [IsAnsiNullDefault]
, [IsAnsiNullsEnabled]
, [IsAnsiPaddingEnabled]
, [IsAnsiWarningsEnabled]
, [IsArithmeticAbortEnabled]
, [IsAutoClose]
, [IsCloseCursorsOnCommitEnabled]
, [IsLocalCursorsDefault]
, [IsNumericRoundAbortEnabled]
, [IsParameterizationForced]
, [IsQuotedIdentifiersEnabled]
, [IsRecursiveTriggersEnabled]
, [IsSyncWithBackup]
, [IsTornPageDetectionEnabled]
FROM #DatabaseDetails
ORDER BY DatabaseName
SET NOCOUNT OFF