IgorMi (11/20/2012)
sql-lover (11/19/2012)
Use COLLATION clause in one of the joins .. 😉If you have a database with a different collation like me, you'll get that error.
Except the collation, your code does not work properly i.e. it does not list sizes for all databases but only for the currently used. The one-line code above is the solution for you.
Regards
IgorMi
That code gives the size of the database, not the used size of the database which is what the OP wants
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),'& # x 0 D ;',CHAR(13) + CHAR(10)
)
EXECUTE sp_executesql @SQL
Just remove the spaces between '& # x 0 D ;'