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

Question

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

I use sp_help to compare the output from different servers to see if the tables are identical. FixedLenNullInSource and TrimTrailingBlanks are throwing my comparisons off.

Answer 1

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.