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. |
|
/*1) UDT is defined but does not exist.*/ select TableName from OUTB /*2) UDF is not registered.*/ select * from sysobjects where [name] like ‘@%’ and name not in /*3) UDF is defined on an unregistered UDT.*/ select T0.TableID from CUFD T0 where left(T0.TableID,1)=’@’ and /*4) UDF is defined but does not exist.*/ select TableID, AliasID from CUFD where not exists ( /*5) UDF valid values are defined for non-existing UDF.*/ select TableId, FieldID from UFD1 T /*6) UDF definition does not match actuality.*/ select T1.UDF, T0.nvarchar_size as ‘act_size’, T1.nvarchar_size as ‘def_size’ from ( /*7a) UDF contains extra spaces in field TableID in User-fields description CUFD table.*/ select * from CUFD /*7b) UDF contains extra spaces in field AliasID in User-fields select * from CUFD /*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 |
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.*/
HELP !!!, Someone has this in HANA ??/*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. */