Visualisation de l'espace dispo FileGroup

1

Create table #AllDbSpace (
    DbName varchar(100),
    FgName varchar(100),
    NbFiles int,
    CurrentSizeMB int,
    FreeSpaceMB int,
    FreeSpacePct int
)

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQLScript VARCHAR(6000)

DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                 where dbid > 4
                ORDER BY [name]

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
        BEGIN

                  SET @SQLScript = 'USE ' + @DatabaseName + ';
                                                  With dbSpace'+ @DatabaseName +' as
                                                    (
                                                        SELECT DB_NAME() AS DbName,
                                                        fg.name AS FgName,
                                                        count(f.name) AS NbFiles,
                                                        sum(f.size/128) AS CurrentSizeMB,
                                                        sum(f.size/128-CAST(FILEPROPERTY(f.name,''SpaceUsed'') AS INT)/128) AS FreeSpaceMB
                                                        FROM sys.database_files f
                                                        inner join sys.filegroups fg on fg.data_space_id = f.data_space_id
                                                        group by fg.name
                                                    )
                                                    INSERT INTO #AllDbSpace
                                                    Select DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpaceMB*100/CurrentSizeMB as ''%FreeSpace''
                                                    FROM dbSpace'+ @DatabaseName +';
                                                    '

                EXEC (@SQLScript)
                FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpacePct as '%FreeSpace' FROM #AllDbSpace
DROP TABLE #AllDbSpace