List All databases by Size and Available space SQL SERVER 2008 R2

Question

I'm a sysadmin but by no means am I a DBA person, and as such I'd like to ask for your help.

I have 2 questions. First, how do I query the SQL server to return a list of all databases (without the default ones like master etc..) and list the DB size and the Available space (preferably in GB):

See Picture

What I also need to know if the "Space available" affects the .bak file size of the respective DB.

Hoping for a quick response. Thanks in advance.

David Sankovsky.

Answer 1

Right, after some tweaking and RTFM-ing I came up with this:

IF EXISTS(
    SELECT 1
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID('tempdb.dbo.#DBObj')
) DROP TABLE #DBObj

CREATE TABLE #DBObj
(
      [Db] SYSNAME
    , reservedpages INT
    , usedpages INT
    , pages INT
)

EXEC sys.sp_MSforeachdb '
    USE [?]

    INSERT INTO #DBObj
    (
          [Db]
        , reservedpages
        , usedpages
        , pages
    )
    SELECT
          DB_NAME()
        , pg.reservedpages
        , pg.usedpages
        , pg.pages
    FROM (
        SELECT
              reservedpages = Sum(a.total_pages)
            , usedpages = SUM(a.used_pages)
            , pages = SUM(
                CASE
                    WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
                    WHEN a.[type] != 1 AND p.index_id < 2 THEN a.used_pages
                    WHEN p.index_id < 2 THEN a.data_pages ELSE 0
                End
              )
        FROM sys.partitions p
        JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
        LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
    ) pg'

SELECT
      d.Name
    , total_size_mb = data.row_size_mb + data.log_size_mb
    , data.log_size_mb
    , data.row_size_mb
    , unused_size_mb = CAST((do.reservedpages - do.usedpages) * 8. / 1024 AS DECIMAL(10,2))
    , unallocated_space_GB =
        CAST(CASE WHEN data.row_size >= do.reservedpages
            THEN (data.row_size - do.reservedpages) * 8. / 1024 /1024
            ELSE 0
        END AS DECIMAL(10,2))
FROM (
    SELECT
          df2.database_id
        , log_size_mb = CAST(df2.log_size * 8. / 1024 AS DECIMAL(10,2))
        , row_size_mb = CAST(df2.row_size * 8. / 1024 AS DECIMAL(10,2))
        , df2.log_size
        , df2.row_size
    FROM (
        SELECT
              df.database_id
            , log_size = SUM(CASE WHEN df.type_desc = 'LOG' THEN df.size END)
            , row_size = SUM(CASE WHEN df.type_desc = 'ROWS' THEN df.size END)
        FROM sys.master_files df
        GROUP BY df.database_id
    ) df2
) data
JOIN sys.databases d ON data.database_id = d.database_id
JOIN #DBObj do ON do.DB = d.name
ORDER BY data.row_size_mb + data.log_size_mb DESC

That seems to get the job done. Feel free to share and thanks for the help everyone. SQL Link