Is there a way to change the values for FixedLenNullInSource and TrimTrailingBlanks?

sql-server, sql-server-2008, sql-server-2008-r2, t-sql

Solution

`TrimTrailingBlanks` relates to the `SET ANSI_PADDING` option when the table was created. You might be able to change that without recreating the whole table in a similar way to my answer here for changing the `ANSI_NULL` option.

Otherwise you would need to recreate the table with the desired semantics selected.

Looking at the definition of `sp_help`

   'FixedLenNullInSource' = 
   CASE
        WHEN Type_name(system_type_id) NOT IN ( 'varbinary', 'varchar', 'binary', 'char' ) THEN '(n/a)'
        WHEN is_nullable = 0 THEN @no
        ELSE @yes
    END 

so it appears different values for `FixedLenNullInSource` just indicate that the nullability of the column is different and that it is one of the 4 specified datatypes. You would need to fix that with `ALTER TABLE ... ALTER COLUMN`

You are probably much better off using a third party tool to compare the databases such as Redgate SQL Compare or SQL Server Data Tools or even just querying `sys.tables` and `sys.columns` yourself rather than using `sp_help` though.

Problem

Original source

Related problems

• Update ANSI_NULLS option in an existing table