Fetch Connected DB Details | SSMS

Fetch Connected DB Details | SSMS

SELECT 
    @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    CONVERT(VARCHAR(25), DATABASEPROPERTYEX(DB_NAME(), 'CreateDate'), 120) AS CreateDate,
    CONVERT(VARCHAR(25), DATABASEPROPERTYEX(DB_NAME(), 'LastBackupDate'), 120) AS LastBackupDate,
    DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS RecoveryModel,
    DATABASEPROPERTYEX(DB_NAME(), 'Status') AS Status,
    DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability,
    DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS UserAccess,
    DATABASEPROPERTYEX(DB_NAME(), 'Version') AS Version,
    (SELECT SUM(size * 8 / 1024) FROM sys.database_files) AS TotalSizeMB,
    (SELECT SUM(CASE WHEN type_desc = 'ROWS' THEN size * 8 / 1024 ELSE 0 END) FROM sys.database_files) AS DataSizeMB,
    (SELECT SUM(CASE WHEN type_desc = 'LOG' THEN size * 8 / 1024 ELSE 0 END) FROM sys.database_files) AS LogSizeMB

You can also query sysdatabase to fetch Master DB file location –

select * from master.sys.sysdatabases
where 1=1 
and dbid = 13

If you know the DBID use the required id, else query using db_name ‘Name‘.