Useful Schema Query – Custom Fields

Todd Hardin from CSI posted a useful SQL query to the SalesLogix Business Partner Newsgroups for for listing all custom fields in your SalesLogix database and I wanted to pass it along. This query will produce a list of SalesLogix fields that you have customized. It will also tell you the MS-SQL datatype. Futhermore, it will tell you if you set the field to be Date vs DateTime in SalesLogix. This is based on the SECTABLEDEFS.USERDEF = ‘T’.

          when (information_schema.columns.data_type = 'datetime') then
                  when sysdba.sectabledefs.DATETIMETYPE = 'D' then 'date'
              else 'datetime'
          when (information_schema.columns.data_type = 'numeric') then
              'numeric' + '(' + cast(numeric_precision as varchar(10)) + ',' +
              cast(numeric_scale as varchar(10)) + ')'
          when (character_maximum_length is not NULL) then
              information_schema.columns.data_type + '(' +
              cast(information_schema.columns.character_maximum_length as varchar(20)) + ')'
          else information_schema.columns.data_type
     end as  DataType
from sysdba.sectabledefs inner join
     on (sysdba.sectabledefs.tablename = information_schema.columns.table_name
         and sysdba.sectabledefs.fieldname =    information_schema.columns.column_name)
where sysdba.sectabledefs.userdef = 'T' and sysdba.sectabledefs.TABLENAME <> 'ATTACHMENT'
order by
    sysdba.sectabledefs.tablename asc,
    sysdba.sectabledefs.userdef desc,
    sysdba.sectabledefs.fieldname asc

This is awesome. Thanks Todd!


Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. It’s a great piece of SQL and ver useful..

    However, Sage SalesLogix has a lot of OOTB fields that are "tagged" "Userdef".. That is, they have the SectableDefs.USerDef = ‘T’ set.

    So when one uses this, be sure and compare the results with a an OOTB "blank" (don’t use the "eval 😉

    — RJLedger


Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!