Get database size With T-SQL and MySQL

I’ve recently been busy documentation various systems at work and came up with these queries to get a list of databases and their sizes for each SQL Server. These queries will show the server name, database names, and their sizes in KB, MB and GB.

For SQL Server 2005 & 2008

-- SQL Server 2005 /2008
CREATE TABLE #databases
(
 DATABASE_NAME VARCHAR(50),
 DATABASE_SIZE FLOAT,
 REMARKS VARCHAR(100)
)

INSERT #Databases EXEC ('EXEC sp_databases');

SELECT @@SERVERNAME AS SERVER_NAME,
    DATABASE_NAME,
    DATABASE_SIZE AS '(KB)',
    ROUND(DATABASE_SIZE / 1024, 2) AS '(MB)',
    ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS '(GB)',
    REMARKS
FROM #databases;

DROP TABLE #databases;

For SQL Server 2000

-- SQL 2000
SELECT @@SERVERNAME AS SERVER_NAME,
    db.name AS DATABASE_NAME,
    SUM(size * 8) AS '(KB)',
    ROUND(SUM(CAST((size * 8) AS FLOAT)) / 1024, 2) AS '(MB)',
    ROUND(((SUM(CAST((size * 8) AS FLOAT)) / 1024) / 1024), 2) AS '(GB)'
FROM sysaltfiles files
INNER JOIN sysdatabases db
 ON db.dbid = files.dbid
GROUP BY db.name

For MySQL. Note the @@hostname variable. This was introduced in MySQL 5.0.3.8 so you’ll need to remove this if you’re using an earlier version.

-- @@hostname variable introduced in MySQL 5.0.38
SELECT @@hostname,
       TABLE_SCHEMA AS `DATABASE`,
       ROUND(SUM(data_length + index_length +data_free) / 1024, 2) '(KB)',
       ROUND(SUM(data_length + index_length +data_free)/ 1024 / 1024, 2) '(MB)',
       ROUND(SUM(data_length + index_length +data_free)/ 1024 / 1024 /1024, 2) '(GB)'
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;