Database Details

CREATE FUNCTION [dbo].[fn_DatabaseDetails] ()
RETURNS @retDBDetails TABLE
(DBName VARCHAR(64),
StateDesc VARCHAR(60),
Recovery_Model_Desc VARCHAR(60),
LastFullBackupDate DATETIME
)
-- --------------------------------------------------------------------------------------------------
-- FUNCTION : [fn_DatabaseDetails]
-- Description : Retrieve database details.
--
-- --------------------------------------------------------------------------------------------------

AS

BEGIN

INSERT @retDBDetails
SELECT d.[name],
CASE
WHEN d.[source_database_id] IS NOT NULL THEN 'SNAPSHOT'
ELSE d.state_desc
END AS State_Desc,
d.recovery_model_desc,
MAX(b.backup_finish_date) AS FullBackupCompleted
FROM master.sys.databases d
LEFT OUTER JOIN [msdb].[dbo].[backupset] b
ON d.[name] = b.database_name
AND b.[type] = 'D'
WHERE d.[name] != 'tempdb'
GROUP BY d.[name],
d.[source_database_id],
d.[state_desc],
d.[recovery_model_desc]


UPDATE @retDBDetails
SET LastFullBackupDate = NULL
WHERE DBName IN (SELECT r.DBName
FROM @retDBDetails r
INNER JOIN master.sys.Databases d
ON r.DBName = d.[name]
AND r.LastFullBackupDate < d.create_date)
RETURN
END
GO