Check size of databases in SQL Server

Script to check size(in MB) consumed by all databases available in a server.

WITH dbsize
AS
(
    SELECT database_id, TYPE, size * 8.0 / 1024 size
    FROM SYS.MASTER_FILES
)
SELECT
    name,
    (SELECT sum(size) from dbsize WHERE TYPE = 0 AND dbsize.database_id = db.database_id) DataFileSizeMB,
    (SELECT sum(size) from dbsize WHERE TYPE = 1 AND dbsize.database_id = db.database_id) LogFileSizeMB
from SYS.DATABASES db ORDER BY LogFileSizeMB DESC

Another script to check database size in Megabytes

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.Datafilesize AS FLOAT)*8)/1024 DatafilesizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM SYS.DATABASES db
    LEFT JOIN (SELECT database_id, SUM(size) Datafilesize FROM SYS.MASTER_FILES WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM SYS.MASTER_FILES WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM SYS.MASTER_FILES WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM SYS.MASTER_FILES WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id