How to list out the available database in the sql sever current connection?

Method 1 : SP_DATABASES

Method 2 : SELECT name FROM SYS.DATABASES

Method 3 : SELECT name FROM SYS.MASTER_FILES

Method 4 : SELECT * FROM SYS.MASTER_FILES — Type=0 for .mdf and type=1 for .ldf

The sp_databases is a system stored procedure it can be listed the database with the size.

The sys.databases will list the databases, created date, modified date and database id along with the other information

The SYS.MASTER_FILES will query the database details like the database id, size, physical storage path and list the both mdf and ldf.