Quick Query to Display Database by Dataspace, Table, and Index

This query just breaks down the current database by dataspace, table, and index, giving you some useful information such as size in MB, pathname of the dataspace file, etc.

-- Shows size of each object in each dataspace in the current database. 
  select db_name()                              as DatabaseName
       , ds.name                                as DataSpaceName
       , ds.type_desc                           as DataSpaceTypeDesc
       , o.name                                 as TableName
       , i.name                                 as IndexName
       , i.type_desc                            as IndexDesc
       , (sum(ps.used_page_count) * 8) / 1024   as IndexSizeMB
       , df.physical_name                       as PathName
    from sys.objects                 o
    join sys.indexes                 i 
      on o.object_id = i.object_id 
    join sys.data_spaces             ds 
      on ds.data_space_id = i.data_space_id
    join sys.database_files          df
      on ds.data_space_id = df.data_space_id
    join sys.dm_db_partition_stats   ps
      on i.object_id = ps.object_id
group by ds.name
       , o.name
       , i.name
       , ds.type_desc
       , i.type_desc
       , df.physical_name
order by ds.name
       , o.name
       , i.name

A quick script to display the total size each database, taking into account multiple MDF and LDF files.

-------------------------------------------------------------------------------
-- Returns the total size of all databases' mdf and ldf files, and the grand   
-- total of the two.                                                           
-------------------------------------------------------------------------------

-- Create the temp table. 
IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

CREATE TABLE ##t 
(
    [Database]   sysname, 
    [File Type]  NVARCHAR(50),
    [Size (MB)]  BIGINT
)

-- Populate the temp table with value for all the mdf and ldf files for all databases.  
DECLARE @CrLf CHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , type_desc '                                            + @CrLf
         + '         , CAST(Size AS BIGINT) * (8 * 1024) / 1024 / 1024 '      + @CrLf
         + '      FROM sys.database_files '

EXEC sp_msforeachdb @Sql

-- Calculate the grand total (mdf files' sizes + ldf files' sizes; add to temp table. 
SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , '' - TOTAL - '' '                                      + @CrLf
         + '         , CAST(SUM(Size) AS BIGINT) * (8 * 1024) / 1024 / 1024 ' + @CrLf
         + '      FROM sys.database_files '
 
EXEC sp_msforeachdb @Sql

-- Display the totals for each database and file type. 
  SELECT [Database], [File Type], SUM([Size (MB)])   AS 'Size (MB)'
    FROM ##t
GROUP BY [Database], [File Type]
ORDER BY [File Type] DESC, [Size (MB)] DESC

IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t