Report of All Databases, File Size and Space available.

If you want to get a list of all the databases and their file sizes and available free space, you can use the following script. 

You can also put a wrapper and make it a stored procedure. 

   DECLARE @db as sysname;

   create table #database_size (DatabaseName varchar(100),

                                 LogicalFileName varchar(50),

                                 GroupId int,

                                 PhysicalFileName varchar(500),

                                 SizeinMB decimal(10,2),

                                 SpaceUsed decimal(10,2),

                                 AvailableSpace decimal(10,2),

                                 PercentUsed decimal(10,2))

   DECLARE DatabaseList CURSOR FAST_FORWARD FOR

                        SELECT [name]

                        FROM   master.sys.databases 

                        WHERE  [database_id]>4

                        AND    state_desc = 'ONLINE'

                        AND    DATABASEPROPERTYEX(name,'Updateability')='READ_WRITE'

    OPEN DatabaseList

    FETCH NEXT FROM DatabaseList INTO @db

    WHILE (@@fetch_status = 0)

    BEGIN

         PRINT N' ';

         PRINT N'Database: ' + @db;

         PRINT ''

         EXEC ('USE [' + @db + '];

                INSERT INTO #database_size  (DatabaseName,LogicalFileName,GroupId,PhysicalFileName,SizeinMB,SpaceUsed,AvailableSpace,PercentUsed)

               SELECT '''+@db+''',RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],

                CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],

                CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2)) AS [Space Used],

                CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS [Available Space],

                CAST((CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]

                FROM sysfiles

                ORDER BY convert(int,groupid) DESC;')

             

            FETCH NEXT FROM DatabaseList INTO @db

       END

       -- Close the cursor now

       CLOSE DatabaseList; 

       DEALLOCATE DatabaseList;

       select * from #database_size

       drop table #database_size