DECLARE @dbs TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)
INSERT INTO @dbs(dbname)
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('model', 'master', 'msdb')
ORDER BY name
CREATE TABLE #tmp123_results (
[Db] NVARCHAR(100),
[FileId] INT,
[LogicalName] NVARCHAR(655),
[AllocatedMB] DECIMAL(12, 2),
[UsedMB] DECIMAL(12, 2),
[FreeMB] DECIMAL(12, 2),
[PercentUsed] DECIMAL(12, 2),
[PercentFree] DECIMAL(12, 2),
[PhysicalName] NVARCHAR(655)
)
DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR
SET @crsSelect = CURSOR FOR
SELECT dbname
FROM @dbs
OPEN @crsSelect
FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @wrkStatement = '['+@curr_db+ '].dbo.sp_executesql'
SET @wrkSql = 'INSERT INTO #tmp123_results(Db,FileId,LogicalName,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,PhysicalName)
SELECT
DB = '''+@curr_db+''',
a.FILEID,
NAME = a.NAME,
ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
FILENAME = a.FILENAME
FROM [' + @curr_db + '].dbo.sysfiles a'
EXEC @wrkStatement @wrkSql
FETCH NEXT FROM @crsSelect INTO @curr_db
END
CLOSE @crsSelect
DEALLOCATE @crsSelect
SELECT * FROM #tmp123_results ORDER BY Db, FileId
DROP TABLE #tmp123_results