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’.

select
     sysdba.sectabledefs.TABLENAME,
     sysdba.sectabledefs.FIELDNAME,
     sysdba.sectabledefs.DISPLAYNAME,
     case
          when (information_schema.columns.data_type = 'datetime') then
              case
                  when sysdba.sectabledefs.DATETIMETYPE = 'D' then 'date'
              else 'datetime'
              end
          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
     information_schema.columns
     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!

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. 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

    Reply

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!