Get Database Information

Use the below T-SQL script to get the database information.

SELECT  
SERVERPROPERTY('InstanceName') AS InstanceName,
        d.name AS 'Database_Name',
        d.create_date AS 'Create_Date' ,
        d.[compatibility_level] AS [Compatibility_Level],
CAST(((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) AS DECIMAL(38,2))  AS 'Total_Database_Size_MB',
        d.state_desc AS 'State_Description',
d.recovery_model_desc AS 'Recovery_Model',
CASE
              WHEN SERVERPROPERTY('ProductMajorVersion')='10' THEN 'SQL Server 2008'
              WHEN SERVERPROPERTY('ProductMajorVersion')='11' THEN 'SQL Server 2012'
              WHEN SERVERPROPERTY('ProductMajorVersion')='12' THEN 'SQL Server 2014'
              WHEN SERVERPROPERTY('ProductMajorVersion')='13' THEN 'SQL Server 2016'
              WHEN SERVERPROPERTY('ProductMajorVersion')='14' THEN 'SQL Server 2017'
              ELSE 'NONE' 
             END AS  'SQL Version',
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS 'Edition',
'PROD' AS 'Environment'
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE d.database_id >4
GROUP BY d.name,d.create_date,d.[compatibility_level],d.state_desc,d.recovery_model_desc
ORDER BY d.name