USE [Master]
GO
SELECT
sysdatabases.name AS 'db_name'
, (select 'file_type' =
CASE
WHEN sysaltfiles.groupid = 1 THEN 'data'
WHEN sysaltfiles.groupid = 0 THEN 'log'
END) as 'file_type'
, sysaltfiles.name AS 'file_name'
, (sysaltfiles.size * 8 / 1024) AS 'file_size(MB)' -- file size in MB
FROM
dbo.sysdatabases
JOIN
dbo.sysaltfiles ON
(dbo.sysdatabases.dbid=dbo.sysaltfiles.dbid)
WHERE
sysdatabases.dbid NOT IN ('1','2','3','4')
ORDER BY
dbo.sysdatabases.name
GO
SELECT
sysdatabases.name AS 'db_name'
, (select 'file_type' =
CASE
WHEN sysaltfiles.groupid = 1 THEN 'data'
WHEN sysaltfiles.groupid = 0 THEN 'log'
END) as 'file_type'
, sysaltfiles.name AS 'file_name'
, (sysaltfiles.size * 8 / 1024) AS 'file_size(MB)' -- file size in MB
FROM
dbo.sysdatabases
JOIN
dbo.sysaltfiles ON
(dbo.sysdatabases.dbid=dbo.sysaltfiles.dbid)
WHERE
sysdatabases.dbid NOT IN ('1','2','3','4')
ORDER BY
dbo.sysdatabases.name