Check size of tables in a database

To check size(in KB) consumed by each table in a database the script is

SELECT
    s.[name] As SchemaName,
    t.NAME AS TableName,
    t.create_date,
    p.rows AS RecordCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    SYS.TABLES t
INNER JOIN
    SYS.SCHEMAS s ON t.schema_id = s.schema_id
INNER JOIN   
    SYS.INDEXES i ON t.OBJECT_ID = i.object_id
INNER JOIN
    SYS.PARTITIONS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    SYS.ALLOCATION_UNITS a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows,t.create_date,s.[name]
ORDER BY
    UsedSpaceKB desc

Modified script to check size of each table in a database in MB

SELECT
    s.[name] As SchemaName,
    t.NAME AS TableName,
    t.create_date,
    p.rows AS RecordCount,
    (CAST(SUM(a.total_pages) AS FLOAT)* 8/1024) AS TotalSpaceMB,
    (CAST(SUM(a.used_pages) AS FLOAT)* 8)/1024 AS UsedSpaceMB,
    (CAST(SUM(a.total_pages) AS FLOAT) - CAST(SUM(a.used_pages) AS FLOAT)) * 8 AS UnusedSpaceMB
FROM
    SYS.TABLES t
INNER JOIN
    SYS.SCHEMAS s ON t.schema_id = s.schema_id
INNER JOIN   
    SYS.INDEXES i ON t.OBJECT_ID = i.object_id
INNER JOIN
    SYS.PARTITIONS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    SYS.ALLOCATION_UNITS a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows,t.create_date,s.[name]
ORDER BY
    UsedSpaceMB desc

One more option to check table sizes in a database is to Right Click on the Database > Reports > Standard Reports > Disk Usage by Table.