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