/*

Why are you using temp table and using subquery? A subquery is not as efficient as a join or a derived table or an exists. The latter being the most optimal. This would have been far more efficient in typing and execution.

You have allocated a data type varchar(50) for the database name when this should have been syname or nvarchar(128) as per the system tables that you are refering to. This would fail for database with a name > 50 characters

I would like to have had the date of the last backup if I'm checking.

*/

DECLARE

@num_of_days int

SELECT @num_of_days = 7

SELECT

a.[name]

,b.DateLogLastBackedUp

,CASE

WHEN b.DateLogLastBackedUp IS NULL THEN 1

ELSE 0

ENDAS TransactionLogNeverBackedUpIndicator

FROM

master.dbo.sysdatabases a

LEFT JOIN

(-- gets the latest available transaction log backup if exists

SELECT

database_name

,MAX(backup_start_date)AS DateLogLastBackedUp

FROM

msdb.dbo.backupset

WHERE

type = 'L'-- Log backups only

AND

DATEDIFF(dd,backup_start_date,GETDATE()) > @num_of_days

GROUP BY

database_name

) b ON a.[name] = b.DateLogLastBackedUp

WHERE

DATABASEPROPERTYEX(a.[name],'Recovery') = 'FULL'