Check for column in backupset table Created a script to show last backup locations, sizes and dates

SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,
                case 
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then cast(round(max(bs.compressed_backup_size/1024),0) as numeric)
                    else cast(round(max(bs.backup_size/1024),0) as numeric)
                end as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in ('master', 'model', 'msdb')
AND x.type IN ('D')
ORDER BY DatabaseName;


The only problem, is that I get the following error when its executed on a SQL Server 2005 Enterprise 9.00.3042 64bit

Msg 207, Level 16, State 1, Line 12
Invalid column name 'compressed_backup_size'.

If I change the then/else to 

case 
when exists(
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
)
then 1
else 0
end as backup_size


I get 1 or 0 for backup_size depending on whether *compressed_backup_size* exists in backupset. 

database_name   type    backup_size backup_finish_date  device_name
model   I   0   2013-09-02 18:30:19.000 Data Protector_(DEFAULT)_model_163022971_0
msdb    I   0   2013-09-02 18:30:22.000 Data Protector_(DEFAULT)_msdb_163022971_0


So in spite of using CASE WHEN THEN ELSE to check for the existence of *compressed_backup_size*, I'm still getting an error. Any thoughts on why the THEN argument is being parsed when the column *compressed_backup_size* clearly doesn't exist ?

Thanks in advance
You have to use dynamic SQL to hide that column from the parser in 2005. All columns are checked to make sure they exist before the query runs.

DECLARE @sql nvarchar(MAX);

SET @sql = N'
SELECT DatabaseName = x.database_name,
        LastBackupDate = x.backup_finish_date,
        BackupSizeKB = x.backup_size,
        LastBackupFileName = x.device_name,
        left(right(x.device_name, 21),17) as Backup_Device_Date,
        type = x.type
        FROM (  SELECT  bs.database_name, bs.type,' +
                case 
                    when exists(
                        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='backupset' and column_name = 'compressed_backup_size'
                    )
                    then N'cast(round(max(bs.compressed_backup_size/1024),0) as numeric)'
                    else N'cast(round(max(bs.backup_size/1024),0) as numeric)'
                end + N' as backup_size,
                max(bs.backup_finish_date) as backup_finish_date,
                max(bmf.physical_device_name) as device_name
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.is_copy_only = 0
          GROUP BY bs.type, bs.database_name) x
WHERE x.database_name not in (''master'', ''model'', ''msdb'')
AND x.type IN (''D'')
ORDER BY DatabaseName;';

EXEC(@sql);