locked
Varchar/char column problems RRS feed

  • Question

  • I've had a lingering problem with columns defined as char. Initially, when creating the database, ANSI-Padding was on and my NOT NULL columns all ended up padded. I went through all the tables and recreated them with padding off and creating the column as varchar. Well, looks like I've missed some.  I found a char field in a table defined as not null, but it says the padding is off and when I check the properties on the column, and found that ANSI PADDING status says false.

    Do I:
    1. 'Update table' setting column=trim(...)?
    2. Add another column (as char or varchar??) with padding off and 'update set newcolumn=trim(oldcolumn)?
    3. Redefine the column as varchar and trim?


    Also.. is there a way to query sysobjects and/or syscolumns for CHAR columns (xtype 175) that are NOT NULL and telling me whether padding is off or on?

     
    Wednesday, February 11, 2009 10:22 PM

Answers

  • You can use this query for a detailed report...

    select 
         object_name(object_id) Table_name,
         name as Column_name,
         case when system_type_id = 175 then 'char' else 'varchar' end
           + '(' +
         case when max_length = -1 then 'MAX' else cast(max_length as varchar(100)) end 
           + ')' as data_type,
         case when is_nullable = 0 then 'Yes' else 'No' end as Nullable,
         case when is_ansi_padded = 0 then 'No' else 'Yes' end as Ansi_Padded

    from 
         sys.columns where system_type_id in( 175, 167)
    and 
         object_id in (select object_id from sys.objects where type = 'U')


    Result will be ....

    Table                 Column                Datatype            Nullable      Ansi_Padded
    INTEGRATELOG    ENTITYNAME         varchar(255)       Yes              Yes
    FILELIST            FILESTATID           varchar(1)           No               Yes
    FILELIST            ATTASTAT             varchar(1)           No               Yes
    A1                    co12                    varchar(MAX)       No               Yes
    ATTAFILE          ATTASTAT            varchar(1)           No               Yes
    ATTAFILE          ATTAFILE              varchar(1)           No               Yes
    INTEGRATELOG   NAME                   varchar(255)       Yes              Yes


    Hope this is useful.

    Thanks, Krishna.


    Krishna
    • Marked as answer by Raymond-Lee Friday, February 20, 2009 9:42 AM
    Friday, February 13, 2009 7:48 AM

All replies

  • What do I do with the current column, though? 1, 2 or 3?
    Thursday, February 12, 2009 8:23 PM
  • You can use this query for a detailed report...

    select 
         object_name(object_id) Table_name,
         name as Column_name,
         case when system_type_id = 175 then 'char' else 'varchar' end
           + '(' +
         case when max_length = -1 then 'MAX' else cast(max_length as varchar(100)) end 
           + ')' as data_type,
         case when is_nullable = 0 then 'Yes' else 'No' end as Nullable,
         case when is_ansi_padded = 0 then 'No' else 'Yes' end as Ansi_Padded

    from 
         sys.columns where system_type_id in( 175, 167)
    and 
         object_id in (select object_id from sys.objects where type = 'U')


    Result will be ....

    Table                 Column                Datatype            Nullable      Ansi_Padded
    INTEGRATELOG    ENTITYNAME         varchar(255)       Yes              Yes
    FILELIST            FILESTATID           varchar(1)           No               Yes
    FILELIST            ATTASTAT             varchar(1)           No               Yes
    A1                    co12                    varchar(MAX)       No               Yes
    ATTAFILE          ATTASTAT            varchar(1)           No               Yes
    ATTAFILE          ATTAFILE              varchar(1)           No               Yes
    INTEGRATELOG   NAME                   varchar(255)       Yes              Yes


    Hope this is useful.

    Thanks, Krishna.


    Krishna
    • Marked as answer by Raymond-Lee Friday, February 20, 2009 9:42 AM
    Friday, February 13, 2009 7:48 AM