Script to list the SQL Server Databases, Size and Utilisation by Specific Application or Service

Using EXEC sp_databases may show the wrong sizes for the DB. Here's a nice, reliable query that will give all database names, sizes and statuses, although not which apps are utilising the DBs:

SELECT
    D.name,
    F.Name AS FileType,
    F.physical_name AS PhysicalFile,
    F.state_desc AS OnlineStatus,
    CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize,
    CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY
    D.name

To get only general information (database name, files and size) you can have some success running the "sp_databases" stored procedure:

exec sp_databases

If the above didn't work in SQL Server 2000, you can try the following:

select *
from sys.sysdatabases

But you can get a more detailed trace and audit data using the "SQL Profiler" that is shipped with SQLServer.

You could use Nagios for your various monitoring tasks. Nagios provides complete monitoring of MSSQL - including availability, database and table sizes, cache ratios, and other key metrics.

Here's a variation on the query above.

    SELECT
        D.Name as [Database Name],
        F.state_desc AS OnlineStatus,
        convert(varchar(12),CAST((sum(
        case when f.physical_name like '%ldf'then 0 else f.size end)*8.0)/1024.0/1024.0 AS numeric(8,2))) AS [Data File Size GB],
        convert(varchar(12),CAST((sum(
        case when f.physical_name like '%ldf'then f.size else 0 end)*8.0)/1024.0/1024.0 AS numeric(8,2))) AS [Log File Size GB]
        --,CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes
    FROM 
        sys.master_files F
        INNER JOIN sys.databases D ON D.database_id = F.database_id
    group by D.name,F.state_desc
    ORDER BY
        D.name

The answer(s) provided here return the size of the database files, which is not necessarily the size of the data in the database. To get the size of data in each database on a SQL Server, use the following query.

DECLARE @r TABLE( name SYSNAME, size NUMERIC(36, 2) )
DECLARE @db SYSNAME
    ,   @sql NVARCHAR(MAX)
    ,   @size NUMERIC(36,2)
--
SELECT  @db = MIN( name ) FROM sys.databases
--
WHILE @db IS NOT NULL
BEGIN
    SET @sql = N'SELECT @size = CAST( ROUND( ( SUM( a.used_pages ) * 8 / ( 1024.00 * 1024.00 ) ), 2) AS NUMERIC( 36, 2) )
    FROM    [' + @db + '].sys.tables t
    LEFT JOIN   [' + @db + '].sys.indexes i
        ON  t.OBJECT_ID = i.object_id
    LEFT JOIN   [' + @db + '].sys.partitions p 
        ON  i.object_id = p.OBJECT_ID
        AND i.index_id = p.index_id
    JOIN    ' + @db + '.sys.allocation_units a 
        ON  p.partition_id = a.container_id'
--
    EXEC sp_executesql @sql, N'@size NUMERIC(36,2) OUTPUT', @size OUTPUT
--
    INSERT INTO @r
    SELECT @db, @size
--  
    SELECT @db = MIN( name ) FROM sys.databases WHERE name > @db    
END
--
SELECT name, SUM(size) AS [size (GB)] FROM @r 
GROUP BY name WITH ROLLUP
ORDER BY 2 DESC