Checking and removing UDF before updating

While upgrading some databases, we sometimes encounter checks stating that differences exists between UDT – UDF defined at the SQL-Server level and what is configured Inside Business One.

One of the “bad side” of these messages is that they don’t tell what are the faulty objects, relying on the administrator to find them alone.

Basically checks have to be done from both side (what is defined in the SQL-Server and is not defined as UDT – UDF and the opposite) and regarding UDF can be done thru the following SQL code:

 -- Search defined UDF which don't exists in system tables select TableID, AliasID from CUFD WHERE NOT TableID + '|' + AliasID IN ( select so.name + '|' + RIGHT (sc.name, len (sc.name) - 2) from syscolumns sc inner join sysobjects so on sc.id = so.id where sc.name like 'U__%' ESCAPE '_' ) -- Search defined columns in SQL-Server which are not defined as UDT select so.name TableName, sc.name ColumnName from syscolumns sc inner join sysobjects so on sc.id = so.id and so.xtype = 'U' where sc.name like 'U__%' ESCAPE '_' AND NOT so.name + '|' + RIGHT (sc.name, len (sc.name) - 2) IN ( select TableID + '|' + AliasID from CUFD ) 

After having checked the content of these messages, it can be usefull to update the datamodel and to remove the objects defined at one side and missing at the other one.

Regarding UDF, it can be done thru the following SQL code:

 DECLARE @TableName sysname DECLARE @ColumnName sysname DECLARE @SQL varchar(MAX) DECLARE cursTableCol CURSOR FOR select so.name TableName, sc.name ColumnName from syscolumns sc inner join sysobjects so on sc.id = so.id and so.xtype = 'U' where sc.name like 'U__%' ESCAPE '_' AND NOT so.name + '|' + RIGHT (sc.name, len (sc.name) - 2) IN ( select TableID + '|' + AliasID from CUFD ) OPEN cursTableCol FETCH NEXT FROM cursTableCol INTO @TableName, @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = 'ALTER TABLE [' + @TableName + '] DROP COLUMN [' + @ColumnName + ']' EXEC (@SQL) FETCH NEXT FROM cursTableCol INTO @TableName, @ColumnName END CLOSE cursTableCol DEALLOCATE cursTableCol 

I know that SAP forbid to directly modify the datamodel, but in this specific case, I just don’t know any other method to correct a database…

Hope it will help somebody!