SQL Server - show list of all database files

select database_id, type_desc, name, physical_name, state_desc, size from sys.master_files;

You can play with this query to display more or less columns, of course. This will give you a list of all files including LOG files, which may not be your cup o' tea. (where type_desc <> 'LOGS')

Cheers