free disk

BEGIN
    SET nocount ON 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#dbfileinfo') ) 
        BEGIN
            DROP TABLE #dbfileinfo
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#logsizestats') ) 
        BEGIN
            DROP TABLE #logsizestats
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#datafilestats') ) 
        BEGIN
            DROP TABLE #datafilestats
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#fixeddrives') ) 
        BEGIN
            DROP TABLE #fixeddrives
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#usados') ) 
        BEGIN
            DROP TABLE #usados
        END 
    CREATE TABLE #fixeddrives
        (
          DriveLetter VARCHAR(10) ,
          MB_Free dec(20, 2)
        ) 
    CREATE TABLE #datafilestats
        (
          DBName VARCHAR(255) ,
          DBId INT ,
          FileId TINYINT ,
          [FileGroup] TINYINT ,
          TotalExtents dec(20, 2) ,
          UsedExtents dec(20, 2) ,
          [Name] VARCHAR(255) ,
          [FileName] VARCHAR(400)
        ) 
    CREATE TABLE #logsizestats
        (
          DBName VARCHAR(255) NOT NULL
                              PRIMARY KEY CLUSTERED ,
          DBId INT ,
          LogFile REAL ,
          LogFileUsed REAL ,
          Status BIT
        )
    CREATE TABLE #dbfileinfo
        (
          [ServerName] VARCHAR(255) ,
          [DBName] VARCHAR(65) ,
          [LogicalFileName] VARCHAR(400) ,
          [UsageType] VARCHAR(30) ,
          [Size_MB] dec(20, 2) ,
          [SpaceUsed_MB] dec(20, 2) ,
          [MaxSize_MB] dec(20, 2) ,
          [NextAllocation_MB] dec(20, 2) ,
          [GrowthType] VARCHAR(65) ,
          [FileId] SMALLINT ,
          [GroupId] SMALLINT ,
          [PhysicalFileName] VARCHAR(400) ,
          [DateChecked] DATETIME
        ) 
    DECLARE @SQLString VARCHAR(3000)
    DECLARE @MinId INT
    DECLARE @MaxId INT
    DECLARE @DBName VARCHAR(255)
    DECLARE @tblDBName TABLE
        (
          RowId INT IDENTITY(1, 1) ,
          DBName VARCHAR(255) ,
          DBId INT
        ) 
    INSERT  INTO @tblDBName
            ( DBName ,
              DBId
            )
            SELECT  [Name] ,
                    DBId
            FROM    master..sysdatabases
            WHERE   ( Status & 512 ) = 0
            ORDER BY [Name] 
    INSERT  INTO #logsizestats
            ( DBName ,
              LogFile ,
              LogFileUsed ,
              Status
            )
            EXEC ( 'dbcc sqlperf(logspace) with no_infomsgs'
                ) 
    UPDATE  #logsizestats
    SET     DBId = DB_ID(DBName) 
    INSERT  INTO #fixeddrives
            EXEC master..xp_fixeddrives 
    SELECT  @MinId = MIN(RowId) ,
            @MaxId = MAX(RowId)
    FROM    @tblDBName 
    WHILE ( @MinId <= @MaxId ) 
        BEGIN
            SELECT  @DBName = [DBName]
            FROM    @tblDBName
            WHERE   RowId = @MinId 
            SELECT  @SQLString = 'SELECT ServerName = @@SERVERNAME,'
                    + ' DBName = ''' + @DBName + ''','
                    + ' LogicalFileName = [name],'
                    + ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'
                    + ' Size_MB = [size]*8/1024.00,' + ' SpaceUsed_MB = NULL,'
                    + ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize/1024.00*8 END,'
                    + ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'
                    + ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'
                    + ' FileId = [fileid],' + ' GroupId = [groupid],'
                    + ' PhysicalFileName= [filename],'
                    + ' CurTimeStamp = GETDATE()' + 'FROM [' + @DBName
                    + ']..sysfiles' 
            PRINT @SQLString 
            INSERT  INTO #dbfileinfo
                    EXEC ( @SQLString
                        ) 
            UPDATE  #dbfileinfo
            SET     SpaceUsed_MB = Size_MB / 100.0 * ( SELECT LogFileUsed
                                                       FROM   #logsizestats
                                                       WHERE  DBName = @DBName
                                                     )
            WHERE   UsageType = 'Log'
                    AND DBName = @DBName 
            SELECT  @SQLString = 'USE [' + @DBName
                    + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS' 
            INSERT  #datafilestats
                    ( FileId ,
                      [FileGroup] ,
                      TotalExtents ,
                      UsedExtents ,
                      [Name] ,
                      [FileName]
                    )
                    EXECUTE ( @SQLString
                           ) 
            UPDATE  #dbfileinfo
            SET     [SpaceUsed_MB] = S.[UsedExtents] * 64 / 1024.00
            FROM    #dbfileinfo AS F
                    INNER JOIN #datafilestats AS S ON F.[FileId] = S.[FileId]
                                                      AND F.[GroupId] = S.[FileGroup]
                                                      AND F.[DBName] = @DBName 
            TRUNCATE TABLE #datafilestats 
            SELECT  @MinId = @MinId + 1
        END 
    SELECT  @@servername AS servidor ,
            SUBSTRING(A.PhysicalFileName, 1, 1) AS unidad ,
            SUM([Size_MB]) AS SqlTotalDB ,
            SUM([SpaceUsed_MB]) AS SqlTotalUsedSpaceDB ,
            SUM(( [Size_MB] ) - ( [SpaceUsed_MB] )) AS SQLTotalFreeSpaceDB
    INTO    #usados
    FROM    #dbfileinfo AS A
            LEFT JOIN #fixeddrives AS B ON SUBSTRING(A.PhysicalFileName, 1, 1) = B.DriveLetter
    GROUP BY SUBSTRING(A.PhysicalFileName, 1, 1) 
    SELECT  servidor ,
            DriveLetter ,
            MB_Free AS RealMb_free ,
            MB_Free + SQLTotalFreeSpaceDB AS MB_FreeNeto ,
            SqlTotalDB ,
            ABS(( SqlTotalDB - SQLTotalFreeSpaceDB )) AS SQLTotalUsedSpaceDB ,
            SQLTotalFreeSpaceDB ,
            ( 100 * ABS(( SqlTotalDB - SQLTotalFreeSpaceDB )) ) / SqlTotalDB AS Porcentaje_Uso_DB
    FROM    #fixeddrives AS f
            INNER JOIN #usados AS z ON z.unidad = f.DriveLetter 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#dbfileinfo') ) 
        BEGIN
            DROP TABLE #dbfileinfo
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#logsizestats') ) 
        BEGIN
            DROP TABLE #logsizestats
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#datafilestats') ) 
        BEGIN
            DROP TABLE #datafilestats
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#fixeddrives') ) 
        BEGIN
            DROP TABLE #fixeddrives
        END 
    IF EXISTS ( SELECT  1
                FROM    tempdb..sysobjects
                WHERE   [Id] = OBJECT_ID('tempdb..#usados') ) 
        BEGIN
            DROP TABLE #usados
        END 
    SET nocount OFF 
END 



SELECT * FROM  sys.master_files