![]() Select name from sysobjects where xtype='U' * 1)Below query will delete all users defined tables which are defined but not exist in SQL Server.*/ Where T1.type = 'U' and T0.name like 'U/_%' escape '/'Īnd (T0.name !='U_NAME' and T1.name not in ('OUSR', 'AUSR')) ![]() Left join CUFD T2 on T2.TableID = T1.name and ('U_' + T2.AliasID) = T0.name Select T1.name, T0.name from sys.columns T0 join sys.objects T1 on T0.object_id = T1.object_id * 7b) UDF contains extra spaces in field AliasID in User-fieldsĪnd ascii(SUBSTRING (AliasID, LEN(AliasID)+1, 1)) =32 * 7a) UDF contains extra spaces in field TableID in User-fields description CUFD table.*/Īnd ascii(SUBSTRING (TableID, LEN(TableID)+1, 1)) =32 Select tableid + '.U_' + aliasid as 'UDF', editsize as 'nvarchar_size'įrom cufd where typeid='A' and editsize>1 (select xtype from systypes where name='nvarchar') 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 T1.UDF, T0.nvarchar_size as 'act_size', T1.nvarchar_size as 'def_size' from ( * 6) UDF definition does not match actuality.*/ Select 1 from CUFD where TableId=T.TableId and FieldID=T.FieldID * 5) UDF valid values are defined for non-existing UDF.*/ Where t0.name=TableID and t1.name='U_'+AliasID)Īnd TableID not in ('BTNT', 'OIBT', 'OSRI', 'SRNT') ![]() Select TableID, AliasID from CUFD where not exists (įrom sysobjects t0 inner join syscolumns t1 * 4) UDF is defined but does not exist.*/ Not exists (select 1 from OUTB T1 where +T1.TableName = * 3) UDF is defined on an unregistered UDT.*/ ![]() ![]() Select from outb where logtable is not null) and xtype='U' Select * from sysobjects where like and name not in Select 1 from sysobjects where xtype='U' and 2) UDF is not registered.*/ * 1) UDT is defined but does not exist.*/ If an issue is related to UDT & UDF, the queries provided bellow will detect the inconsistencies known.Ģ) UDF is defined on a non-existing table.ģ) UDF is defined on an unregistered UDT.ĥ) UDF valid values are defined for a non-existing UDF.Ħ) UDF definition does not match actuality.ħ) UDF contains extra spaces in field TableID or AliasID in CUFD table. This note provides a few generic queries to detect some common issues about UDT & UDF. User-Defined Table (UDT) and User-Defined Field (UDF) related issues often have different behaviors. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |