
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!
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