Skip to Content
Author's profile photo Former Member

UDT & UDF related issues

User-Defined Table (UDT) and User-Defined Field (UDF) related issues often have different behaviors. This note provides a few generic queries to detect some common issues about UDT & UDF. If an issue is related to UDT & UDF, the queries provided bellow will detect the inconsistencies known.

Description of the bug:

1) UDT is defined but does not exist.
2) UDF is defined on a non-existing table.
3) UDF is defined on an unregistered UDT.
4) UDF is defined but does not exist.
5) UDF valid values are defined for a non-existing UDF.
6) UDF definition does not match actuality.
7) UDF contains extra spaces in field TableID or AliasID in CUFD table.
8) UDF exists but is not defined

/*1) UDT is defined but does not exist.*/

select TableName from OUTB
where not exists (
select 1 from sysobjects where xtype=’U’ and name=’@’+TableName
)

/*2) UDF is not registered.*/

select * from sysobjects where [name] like ‘@%’ and name not in
(select ‘@’+tablename from outb
union all
select ‘@’+logtable from outb where logtable is not null) and xtype=’U’

/*3) UDF is defined on an unregistered UDT.*/

select T0.TableID from CUFD T0 where left(T0.TableID,1)=’@’ and
not exists (select 1 from OUTB T1 where ‘@’ +T1.TableName =
T0.TableID or ‘@’+T1.LogTable= T0.TableID)

/*4) UDF is defined but does not exist.*/

select TableID, AliasID from CUFD where not exists (
select t0.name, t1.name
from sysobjects t0 inner join syscolumns t1
on t0.xtype=’U’ and t0.id=t1.id
where t0.name=TableID and t1.name=’U_’+AliasID)
and TableID not in (‘BTNT’, ‘OIBT’, ‘OSRI’, ‘SRNT’)

/*5) UDF valid values are defined for non-existing UDF.*/

select TableId, FieldID from UFD1 T
where not exists (
select 1 from CUFD where TableId=T.TableId and FieldID=T.FieldID
)

/*6) UDF definition does not match actuality.*/

select T1.UDF, T0.nvarchar_size as ‘act_size’, T1.nvarchar_size as ‘def_size’ from (
select T2.name + ‘.’ + T3.name as ‘UDF’, T3.length/2 as ‘nvarchar_size’ from sysobjects T2 inner join syscolumns T3 on T2.id=T3.id where T2.xtype=’U’ and T3.xtype in
(select xtype from systypes where name=’nvarchar’)
) T0
inner join (
select tableid + ‘.U_’ + aliasid as ‘UDF’, editsize as ‘nvarchar_size’
from cufd where typeid=’A’ and editsize>1
) T1
on T0.UDF=T1.UDF
where T0.nvarchar_size>T1.nvarchar_size

/*7a) UDF contains extra spaces in field TableID in User-fields description CUFD table.*/

select * from CUFD
where datalength(TableID)<>LEN(TableID)
and ascii(SUBSTRING (TableID, LEN(TableID)+1, 1)) =32

/*7b) UDF contains extra spaces in field AliasID in User-fields
description CUFD table*/

select * from CUFD
where datalength(AliasID)<>len(AliasID)
and ascii(SUBSTRING (AliasID, LEN(AliasID)+1, 1)) =32

/*8) UDF exists but is not defined*/

select T1.name, T0.name from sys.columns T0 join sys.objects T1 on T0.object_id = T1.object_id
left join CUFD T2 on T2.TableID = T1.name and (‘U_’ + T2.AliasID) = T0.name
where T1.type = ‘U’ and T0.name like ‘U/_%’ escape ‘/’
and (‘U_’ + T2.AliasID) is null
and (T0.name !=’U_NAME’ and T1.name not in (‘OUSR’, ‘AUSR’))

Update Query with description:

/*1)Below query will delete all users defined tables which are defined but not exist in SQL Server.*/

delete OUTB where ‘@’+TableName not in (
select name from sysobjects where xtype=’U’
)

/* 2 ) We recommend to remove the tables displayed by the query from the database directly in the database interface. Please consider this solution according to your customization. */

/*3)  Below query will delete all users defined fields which are defined on an unregistered UDT.*/

delete from CUFD where left(TableID,1)=’@’ and not exists (
select 1 from OUTB where ‘@’+TableName = TableID or
‘@’+LogTable = TableID)

/*4) Below query will delete all users defined fields which are  defined but not exist in SQL Server.*/

delete CUFD where not exists (
select t0.name, t1.name
from sysobjects t0 inner join syscolumns t1
on t0.xtype=’U’ and t0.id=t1.id
where t0.name=TableID and t1.name=’U_’+AliasID)
and TableID not in (‘BTNT’, ‘OIBT’, ‘OSRI’, ‘SRNT’)

/*5)  Below query will delete all users defined fields’ valid values which are defined for non-exist UDF.*/

delete UFD1 where not exists (
select 1 from CUFD where TableId=UFD1.TableId and FieldID=UFD1.FieldID
)

/*6) Update the size of the UDF in Application.*/

/*7)Below query will trim extra spaces in the table name. */

/*7a) extra spaces in TableID of CUFD:*/

update CUFD
set TableID = replace (TableID,’ ‘, ”) where datalength(TableID)<> LEN
(TableID) and ascii(SUBSTRING (TableID, LEN(TableID)+1, 1)) =32

/*7b) extra spaces in AliasID of CUFD:*/

update CUFD
set AliasID = replace (AliasID,’ ‘, ”) where datalength(AliasID)<> LEN
(AliasID) and ascii(SUBSTRING (AliasID, LEN(AliasID)+1, 1)) =32

/*8) We recommend to remove the columns displayed by the query from the database directly in the database interface. Please consider this solution according to your customization.*/

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alejandro de la garza
      Alejandro de la garza

       

      HELP !!!, Someone has this in HANA ??
      Author's profile photo Tomasz Kowalczyk
      Tomasz Kowalczyk

      /*1) UDT is defined but does not exist.*/

      select T0."TableName" from OUTB T0 where not exists (select 1 from sys.tables T1 where T1.schema_name = current_schema and T1.is_system_table = 'FALSE' and T1.table_name = '@' || "TableName");

       

      /*2) UDF is not registered.*/

      select * from sys.tables

      where schema_name = current_schema and is_system_table = 'FALSE' and table_name like '@%'

      and table_name not in

      (

      select '@' || "TableName" from outb

      union all

      select '@' || "LogTable" from outb where "LogTable" is not null

      );

       

      /*3) UDF is defined on an unregistered UDT.*/

      select T0."TableID" from CUFD T0

      where left(T0."TableID",1)='@'

      and not exists (select 1 from OUTB T1 where '@' || T1."TableName" = T0."TableID" or '@' || T1."LogTable"= T0."TableID");

      /*4) UDF is defined but does not exist.*/

      select "TableID", "AliasID" from CUFD

      where not exists

      (

      select 1 from sys.tables t0 inner join sys.columns t1 on t0.table_oid=t1.table_oid

      where t0.schema_name = current_schema and t0.is_system_table = 'FALSE'

      and t0.table_name="TableID" and t1.column_name='U_' || "AliasID")

      and "TableID" not in ('BTNT', 'BTNT1', 'OIBT', 'OSRI', 'SRNT', 'SRNT1', 'ODIB', 'ODSR'

      );

      /*5) UDF valid values are defined for non-existing UDF.*/

      select "TableID", "FieldID" from UFD1 T

      where not exists (select 1 from CUFD where "TableID"=T."TableID" and "FieldID"=T."FieldID");

      /*6) UDF definition does not match actuality.*/

      select T2.table_name, T3.column_name "UDF", T3.length, T0."EditSize" from sys.tables T2

      inner join sys.columns T3 on t2.table_oid=t3.table_oid

      inner join cufd T0 on T0."TableID" = T2.table_name and ('U_' || T0."AliasID") = T3.column_name

      where t2.schema_name = current_schema and T2.is_system_table = 'FALSE'

      and T3.data_type_name ='NVARCHAR'

      and T0."TypeID"='A' and T0."EditSize">1

      and T3.length>T0."EditSize" ;

      /*7a) UDF contains extra spaces in field TableID in User-fields description CUFD table.*/

      select "TableID", "AliasID", "Descr" from CUFD

      where ascii("TableID") = 32;

      /*7b) UDF contains extra spaces in field AliasID in User-fields description CUFD table*/

      select "TableID", "AliasID", "Descr" from CUFD

      where ascii("AliasID") = 32;

      /*8) UDF exists but is not registered*/

      select T2.table_name, T1.column_name "UDF" from sys.columns T1

      join sys.tables T2 on t1.table_oid=t2.table_oid

      left join CUFD T0 on T2.table_name = T0."TableID" and T1.column_name = ('U_' || T0."AliasID")

      where t2.schema_name = current_schema and T2.is_system_table = 'FALSE'

      and T1.column_name like 'U/_%' escape '/'

      and ('U_' || T0."AliasID") is null

      and T2.table_name not in ('OUSR', 'AUSR', 'TDIB', 'TIBT', 'TDSR', 'TSRI');

       

      /* Note! for point 2 and 8 the resultset includes columns created both on SAP Business One system tables and user tables.

      However only columns created on SAP Business One tables should be considered as those are potentially harmful.

      However the list of the SAP Business One system tables is not available as it may differ from patch to patch. */