Find is_ansi_padded status of columns

SELECT b.name as 'schema_name', a.name as 'table_name', a.type_desc

       , c.name, c.column_id

       , d.name as [type_name], c.max_length, c.precision, c.scale, c.is_nullable, c.is_ansi_padded, is_identity

FROM sys.types d INNER JOIN (sys.columns c INNER JOIN (sys.objects a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id)

              ON c.object_id = a.object_id)

              ON d.user_type_id = c.user_type_id

WHERE b.name like '%snapshot%'

       and is_ansi_padded = 0 and d.name NOT IN ('int', 'datetime', 'date', 'numeric', 'float', 'bigint', 'smallint')

ORDER BY a.[type], b.name, a.name, c.column_id

To fix

TrimTrailingBlanks "no" is the same as ANSI_PADDING ON.  So it looks like those columns were (correctly) created with SET ANSI_PADDING ON.

If you have only a few columns with TrimTrailingBlanks "yes", you can create a new column, migrate data and rename like the example below, keeping in mind that you will need do drop constraints and indexes on the column(s) and recreate afterward.  You might find it easier to recreate the table if you have many columns.

SET ANSI_PADDING ON;
GO
ALTER TABLE dbo.MyTable
    ADD MyColumn_New varchar(100);
GO
UPDATE dbo.MyTable
    SET MyColumn_New = MyColumn;
GO
ALTER TABLE dbo.MyTable
    DROP COLUMN MyColumn;
GO
EXEC sp_rename 'dbo.MyTable.MyColumn_New', 'MyColumn', 'COLUMN';
GO