controleren van consistentie op UDT (User Defined Tables)

controleren van consistentie op UDT (User Defined Tables)

het kan soms voorkomen dat de UDT’s van SAP corrupt raken. Om hiervan zeker te zijn, kunnen een aantal controle queries en ook “opruim queries” uitgevoerd worden:

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

 

Een gedachte aan “controleren van consistentie op UDT (User Defined Tables)

  1. Leigh Bhatnagar

    Just neediness to utter your article is as surprising. The clarity in your post is just outstanding and i can feign you’re an practiced on this subject. Fine with your permission allow me to grab your feed to keep positive to date with helpful post. Thankfulness a million and please have in stock resting on the enjoyable work.

Geef een reactie

%d bloggers liken dit: