Adding Missing DB Columns to the SECTABLEDEFS Table in Infor CRM (Formerly Saleslogix)

Here is a SQL script I came up with to add any fields that exist in a Infor CRM enabled table (it exists in the REYNCTABLEDEFS table) but that are not correctly defined in the SECTABLEDEFS table.  This type of thing usually happens if someone adds a new database field outside of the Infor CRM tools, like using the SQL Management studio wizards, or a SQL script directly.

Boiler plate warning:  Running SQL on your database without knowing what you are doing can cause all kinds of bad things.  Run these scripts only if you understand what they are doing.  ALWAYS RUN THIS TYPE OF THING IN A TEST SYSTEM FIRST!

--Finds columns in tables that exist in RESYNCTABLEDEFS but are not
--properly defined in the SECTABLEDEFS.
select
db.tablename tablename,
db.fieldname fieldname
from
(select a.name tablename, b.name fieldname, b.xusertype from sysobjects a inner join syscolumns b on a.id=b.id where a.xtype='U') db
left join
sysdba.sectabledefs b on db.tablename=b.tablename and db.fieldname=b.fieldname
inner join
sysdba.RESYNCTABLEDEFS c on db.tablename=c.TABLENAME
where
b.FIELDNAME is null


--This script actually fixes the SECTABLEDEFS so any real DB columns not in the SECTABLEDEF
--are created.  This assumes any DateTime data type should be set to store in UTC format.
declare @maxoffset int
declare @maxindex int
set @maxindex = (select MAX(fieldindex) from sysdba.SECTABLEDEFS)
set @maxoffset = (select MAX(fieldoffset) from sysdba.SECTABLEDEFS)


insert into sysdba.SECTABLEDEFS
(TABLENAME, FIELDNAME, FIELDOFFSET, FIELDINDEX, SEQCODE, KEYFLAG, SYNC, USERDEF, DISPLAYNAME, ISHIDDEN,
DATETIMETYPE, DESCRIPTION, AUTOINCREMENT, MODIFYDATE, MODIFYUSER, CREATEDATE, CREATEUSER)
select
upper(db.tablename) tablename,
upper(db.fieldname) fieldname,
case when c.SECURE='T' then @maxoffset + ROW_NUMBER() over(order by db.tablename, db.fieldname)
else 0 end fieldoffset,
@maxindex + ROW_NUMBER() over(order by db.tablename, db.fieldname) fieldindex,
'2' SEQCODE,
'0' keyflag,
'Y' sync,
'T' userdef,
db.fieldname Displayname,
'F' isHidden,
case when t.name='datetime' then 'U'
else null end datetimetype,
null description,
'F' autoincrement,
GETUTCDATE() modifydate,
'ADMIN' modifyuser,
GETUTCDATE() createdate,
'ADMIN' createuser
from
(select a.name tablename, b.name fieldname, b.xusertype from sysobjects a inner join syscolumns b on a.id=b.id where a.xtype='U') db
left join
sysdba.sectabledefs b on db.tablename=b.tablename and db.fieldname=b.fieldname
inner join
sysdba.RESYNCTABLEDEFS c on db.tablename=c.TABLENAME
left JOIN
sys.types AS t ON db.xusertype=t.user_type_id
where
b.FIELDNAME is null