html code

1234567891011121314151617181920212223242526272829303132333435
use master
go
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
 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 /*NOT IN (536,528,540,2584,1536,512,4194841)*/  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],  [DBName],  [LogicalFileName],  [UsageType]  AS SegmentName,  B.MB_Free  AS FreeSpaceInDrive,  [Size_MB],  [SpaceUsed_MB],  [Size_MB] - [SpaceUsed_MB]  AS FreeSpace_MB,  Cast(( [Size_MB] - [SpaceUsed_MB] ) / [Size_MB] AS DECIMAL(4, 2))  AS  FreeSpace_Pct,  [MaxSize_MB],  [NextAllocation_MB],  ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )  AS alert_switch,  ( B.MB_Free ) + ( ( [Size_MB] - [SpaceUsed_MB] ) -  ( [NextAllocation_MB] ) ) AS  will_be_on_drive,  CASE MaxSize_MB  WHEN -1 THEN Cast(Cast(( [NextAllocation_MB] / [Size_MB] ) * 100 AS INT  ) AS  VARCHAR(10)) +  ' %'  ELSE 'Pages'  END  AS [GrowthType],  [FileId],  [GroupId],  [PhysicalFileName],  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Status'))  AS Status,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Updateability'))  AS Updateability,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Recovery'))  AS RecoveryMode,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'UserAccess'))  AS UserAccess,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Version'))  AS Version,  [DateChecked]  FROM #dbfileinfo AS A  LEFT JOIN #fixeddrives AS B  ON Substring(A.PhysicalFileName, 1, 1) = B.DriveLetter  ORDER BY ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )
 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
 SET nocount OFF END