none
query to get db list and size RRS feed

  • Question

  • Hi All,

    I need to retrieve list of all databases and the size occupied by them in sql server. can any one share a query. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.

    Regards,

    Varun

    Thursday, November 19, 2015 9:02 AM

Answers

  • Hi,

    Check if it works:

    SELECT d.name,
    ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
    FROM sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id
    WHERE d.database_id > 4 -- Skip system databases
    GROUP BY d.name
    ORDER BY d.name

    Antonio Cesar

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 19, 2015 9:22 AM
    • Marked as answer by Raje14 Thursday, November 19, 2015 10:03 AM
    Thursday, November 19, 2015 9:11 AM

All replies

  • Hi,

    Check if it works:

    SELECT d.name,
    ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
    FROM sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id
    WHERE d.database_id > 4 -- Skip system databases
    GROUP BY d.name
    ORDER BY d.name

    Antonio Cesar

    • Proposed as answer by Ramesh Babu Vavilla Thursday, November 19, 2015 9:22 AM
    • Marked as answer by Raje14 Thursday, November 19, 2015 10:03 AM
    Thursday, November 19, 2015 9:11 AM
  • Try the below?

    Select A.name,B.size,B.type_desc From sys.databases A
    inner join sys.master_files B on A.database_id = B.database_id


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Thursday, November 19, 2015 9:15 AM
  • SELECT [Database Name] = DB_NAME(database_id),

           [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                         WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                         ELSE Type_Desc END,

           [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

    FROM   sys.master_files
    GROUP BY      GROUPING SETS

                  (

                         (DB_NAME(database_id), Type_Desc),

                         (DB_NAME(database_id))

                  )

    ORDER BY      DB_NAME(database_id), Type_Desc DESC

    GO

    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, November 19, 2015 9:24 AM
  • SELECT [Database Name] = DB_NAME(database_id),

           [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                         WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                         ELSE Type_Desc END,

           [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

    FROM   sys.master_files
    GROUP BY      GROUPING SETS

                  (

                         (DB_NAME(database_id), Type_Desc),

                         (DB_NAME(database_id))

                  )

    ORDER BY      DB_NAME(database_id), Type_Desc DESC

    GO

    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, November 19, 2015 9:24 AM