DECLARE @dbname VARCHAR(200),@sql VARCHAR(8000)
SET @sql = '' SET @dbname = ''
IF OBJECT_ID('tempdb..#T_Drive') IS NOT NULL DROP TABLE #T_Drive
IF OBJECT_ID('tempdb..#T_LogSpace') IS NOT NULL DROP TABLE #T_LogSpace
IF OBJECT_ID('tempdb..#T_DBInfo') IS NOT NULL DROP TABLE #T_DBInfo
IF OBJECT_ID('tempdb..#T_FileSpace') IS NOT NULL DROP TABLE #T_FileSpace
IF OBJECT_ID('tempdb..#T_Database') IS NOT NULL DROP TABLE #T_Database
CREATE TABLE #T_Drive([DriveName] VARCHAR(5) PRIMARY KEY,[FreeDriveSpace] BIGINT)
INSERT INTO #T_Drive EXEC master..xp_fixeddrives
CREATE TABLE #T_LogSpace ([DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
[LogSize] MONEY NOT NULL,[LogPercentUsed] MONEY NOT NULL,[LogStatus] INT NOT NULL)
SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'
INSERT INTO #T_LogSpace EXEC(@sql)
CREATE TABLE #T_DBInfo (
[DBName] VARCHAR(200),[FileLogicalName] VARCHAR(200),[FileID] INT NOT NULL,[Filename] VARCHAR(250) NOT NULL,
[Filegroup] VARCHAR(100) NOT NULL,[FileCurrentSize] BIGINT NOT NULL,[FileMaxSize] VARCHAR(50) NOT NULL,
[FileGrowth] VARCHAR(50) NOT NULL,[FileUsage] VARCHAR(50) NOT NULL,[FileGrowthSize] BIGINT NOT NULL)
CREATE TABLE #T_Database ( [DBName] VARCHAR(200) PRIMARY KEY)
INSERT INTO #T_Database
SELECT DBName = LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
WHERE category IN ('0', '1','16')AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' ORDER BY name
CREATE TABLE #T_FileSpace ( [DBName] VARCHAR(200) NULL,[Fileid] INT NOT NULL,
[FileGroup] INT NOT NULL,[TotalExtents] MONEY NOT NULL,[UsedExtents] MONEY NOT NULL,
[FileLogicalName] sysname NOT NULL,[Filename] VARCHAR(1000) NOT NULL)
SELECT @dbname = MIN(dbname) FROM #T_Database
WHILE @dbname IS NOT NULL
BEGIN
SET @sql = 'USE ' + @dbname + '
INSERT INTO #T_DBInfo (
[DBName],[FileLogicalName],[FileID],[Filename],[Filegroup],[FileCurrentSize],
[FileMaxSize],[FileGrowth],[FileUsage],[FileGrowthSize])
SELECT DBName = ''' + @dbname + ''',FileLogicalName = SF.name,
FileID = SF.fileid, Filename = SF.filename, Filegroup = ISNULL(filegroup_name(SF.groupid),''''),
FileCurrentSize = (SF.size * 8)/1024, FileMaxSize =CASE SF.maxsize WHEN -1 THEN N''Unlimited''
ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END,
FileGrowth = (case SF.status & 0x100000 when 0x100000 then
convert(varchar(3), SF.growth) + N'' %''
else
convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end),
FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
ELSE
((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
END
FROM sysfiles SF
WHERE filename like ''%.ldf''
ORDER BY SF.fileid'
EXEC(@sql)
SET @sql = 'USE ' + @dbname + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'
INSERT INTO #T_FileSpace ([Fileid],[FileGroup],[TotalExtents],[UsedExtents],[FileLogicalName],[Filename])
EXEC (@sql)
UPDATE #T_FileSpace SET [DBName] = @dbname WHERE ISNULL([DBName],'') = ''
SELECT @dbname = MIN(dbname) FROM #T_Database WHERE dbname > @dbname
END
SELECT 'DBName' = DFI.DBName,
'FileLogicalName' = DFI.FileLogicalName,
'Filename' = DFI.[Filename],
'FileMBSize' = DFI.FileCurrentSize,
'FileGrowth' = DFI.FileGrowth,
'FileMBGrowth' = DFI.FileGrowthSize,
'DriveName' = TD.DriveName,
'DriveMBEmpty' = TD.FreeDriveSpace,
'FileMBUsed' = CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'LogSpaceUsed' = LSP.LogPercentUsed
FROM #T_DBInfo DFI
LEFT OUTER JOIN #T_Drive TD
ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(TD.DriveName))
LEFT OUTER JOIN #T_FileSpace FS
ON LTRIM(RTRIM(FS.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #T_LogSpace LSP
ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))
ORDER BY 1
