Chubb

I have a script which I use to find the used and free space in each datafile and here it goes

/******************************************************/

USE TEMPDB

CREATE TABLE FileDetails (    

        DbName varchar(100), FileId int , FileGroupName Varchar(50), TotalExtents int , UsedExtents int , [MaxSize] int,

        Name nvarchar( 128 )  , FileName nvarchar( 500 ) ,    

        TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,    

        UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )    

)   

/** This script will let you find the database size in TotalExtents & UsedExtents **/

BEGIN

   /* Get data file(s) size */

   DECLARE @db VARCHAR(50), @cmd VARCHAR(2000), @SQL01 NVARCHAR (400)

   DECLARE dcur CURSOR LOCAL FAST_FORWARD

   FOR

               SELECT CATALOG_NAME

               FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN  ('Northwind','Pubs','Model','Master','MSDB')

               OPEN dcur

               FETCH NEXT FROM dcur INTO @db

               WHILE @@FETCH_STATUS=0

                           BEGIN

                        /** Creation of  Temporary Table in TempDB**/

                                    CREATE TABLE #FileDetails (    

                                           FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,    

                                            Name nvarchar( 128 )  , FileName nvarchar( 500 ) ,    

                                            TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,    

                                            UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )    

                                    )   

                                   SET @cmd = 'use ' + @db + ' DBCC showfilestats'

                                    INSERT INTO #FileDetails (FileId , FileGroupId , TotalExtents , UsedExtents , Name , Filename)    

                                   EXEC(@cmd)

                                    SET @SQL01 =

                        'INSERT INTO FileDetails (DBName, FileId , FileGroupName , TotalExtents , UsedExtents , Name ,    Filename)    

                                    SELECT ' + '''' + @db + '''' + ', A.FileId , B.GroupName , A.TotalExtents , A.UsedExtents ,

                                    A.Name , A.Filename FROM #FileDetails A Left Outer Join ' + @db + '..SysFileGroups B On   A.FileGroupId = B.GroupId'

                                    EXEC SP_EXECUTESQL @SQL01                    

                                    DROP TABLE #FileDetails

                                  FETCH NEXT FROM dcur INTO @db

                            END

END

                        UPDATE FileDetails SET [MaxSize] = B.[MaxSize]

                                    FROM FileDetails A

                                    INNER Join Master..Sysaltfiles B

                                    ON A.FileId = B.FileId and A.[Name] = B.[Name]

CLOSE dcur

DEALLOCATE dcur

/*********************************************************/

You may want to customize the resultset queries of the FileDetails to get what you want!