UDT & UDF related issues

[Former Member]

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. 😎 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

/*😎 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

/*😎 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.*/