just FYI that "is_ansi_padded" column in sys.all_columns will show as 0 if the column type is not a char, binary or variant column. If you are trying to find all columns with the ansi_padding where it actually matters you may want to limit it to those columns with something like the below.

SELECTt.name as [table_name],

c.name as [column_name],

ty.[name] as [column_data_type],

CASEWHEN c.is_ansi_padded = 1

THEN 'On'

ELSE 'Off'

END as [ansi_padding]

FROM sys.all_columns c

inner join sys.tables t

on (c.object_id = t.object_id)

inner join sys.types ty

on (c.system_type_id = ty.system_type_id)

wheret.name not like 'sys%'

and t.name not like 'MS_%'

and ty.name in ('char','varchar','binary','sql_variant','nvarchar','nchar')

order by t.name