If you want to see more information than just the virtual log files count, you can include DBCC SQLPERF(logspace) and include the VLF counts like this. I prefer to test for the existence of temp tables at the start of execution and drop them if necessary, rather than dropping them at the end... this leaves the temp tables available for further use within the context of my current connection, should I want to refactor my query (or whatever other reason makes sense at the time).
IF OBJECT_ID('tempdb.dbo.#stage') IS NOT NULL
DROP TABLE #stage ;
IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
DROP TABLE #results ;
IF OBJECT_ID('tempdb.dbo.#logspace') IS NOT NULL
DROP TABLE #logspace ;
GO
CREATE TABLE #stage
( RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] INT
, Parity INT
, CreateLSN NUMERIC(38)
) ;
GO
CREATE TABLE #results
( DatabaseName SYSNAME
, VLF_count INT
) ;
GO
CREATE TABLE #logspace
( DatabaseName SYSNAME
, [LogSize(MB)] NVARCHAR(127)
, [LogSpaceUsed(%)] NVARCHAR(127)
, [Status] INT
);
GO
EXEC sp_MSforeachdb
N'USE [?];
INSERT INTO #stage
EXEC sp_executesql N''DBCC LogInfo([?])'';
INSERT INTO #results
SELECT DB_NAME(), COUNT(*)
FROM #stage;
TRUNCATE TABLE #stage;'
INSERT INTO #logspace
EXEC sp_executesql N'DBCC SQLPERF(logspace);'
GO
SELECT #logspace.*, #results.VLF_count
FROM #logspace
JOIN #results ON #logspace.DatabaseName = #results.DatabaseName
ORDER BY CAST([LogSize(MB)] AS NUMERIC(38)) DESC
GO