Drop a table with unusual name

Database

Drop a table with unusual name

Hello,

 

I have a table with a name like this "[V_TDAF_PERF_LON_NON_Prime] UNION ALL SELECT distinct [ACCTKEY],[entry_date], [application_id] FROM [V_TDAF_PERF_LOAN_Prime]FROM TDFS_SBOX.ViLONTST", which resulted from my mistake when using the Fastload utility to import data. I tried to drop it but Teradata cannot find the name, eventhough if I use the below query that the table is found, and returned '[V_TDAF_PERF_LON_NON_Prime] UN'. I do not have the permission to create procedure. Please let me know if there are any other ways to drop the table. 

 

SEL tablename
FROM dbc.tables
WHERE tablename like '[V_TDAF_PERF_LON_NON_Prime]%'
AND databasename = 'TDFS_SBOX'

 

Thank you,

Vi

8 REPLIES
Highlighted
Teradata Employee

Re: Drop a table with unusual name

Did you try using double quotes?

 

drop table "database name"."table name";

 

 

Junior Contributor

Re: Drop a table with unusual name

Switch to dbc.TablesV (dbc.Tables truncates the tablename to 30 chars & is deprecated since TD12) and check the actual name (probably truncated after 128 characters).

 

Ad then use a double quoted name to drop the table:

DROP TABLE "[V_TDAF_PERF_LON_NON_Prime] UNION ALL SELECT distinct [ACCTKEY],[entry_date], [application_id] FROM";

 

 

Re: Drop a table with unusual name

Hi,

 

I tried but it gave me the 3807: ' does not exist error.

 

Vi

Re: Drop a table with unusual name

Hi,

So i ran the below query, it gave me the name "[V_TDAF_PERF_LON_NON_Prime]UNION ALLSELECT distinct [ACCTKEY],[entry_date], [application_id] FROM [V_TDAF_PERF_LOAN_Prime]" (all the spaces are exactly so)

 

 

SEL tablename 
FROM dbc.tablesV
WHERE tablename like ('[V_TDAF_PERF_LON_NON_Prime]%')
AND databasename = 'TDFS_SBOX'

However, to test it again, I added 'UNION' into the search criteria, and the table cannot be found this time.

 

SEL tablename 
FROM dbc.tablesV
WHERE tablename like ('[V_TDAF_PERF_LON_NON_Prime]UNION%')
AND databasename = 'TDFS_SBOX'

Regards,

Vi

Junior Contributor

Re: Drop a table with unusual name

Can you successfully drop the table with the returned name?

 

There probably was a line break before the UNION, maybe your client removes it?

Add CHAR2HEXINT (TableName) and check the hex representation.

Teradata Employee

Re: Drop a table with unusual name

I doubt if the identifier mentioned is the actual table name as it is longer than 128 characters. If I shorten the name I can create it and drop it with BTEQ:

 

ct
"[V_TDAF_PERF_LON_NON_Prime] UNION ALL SELECT distinct [ACCTKEY],[entry_date], [application_id] FROM [V_TDAF_PERF_LOAN_Prime]" (myint integer);

 *** Table has been created.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
drop table "[V_TDAF_PERF_LON_NON_Prime] UNION ALL SELECT distinct [ACCTKEY],[entry_date], [application_id] FROM [V_TDAF_PERF_LOAN_Prime]";

 

Try adding a '%' metacharacter to the begining of your LIKE expression:

'%[V_TDAF_PERF_LON_NON_Prime]UNION%'

 

You could also search for long table names:

SEL tablename
FROM dbc.tablesV
WHERE characters(tablename) > 120  
AND databasename = 'TDFS_SBOX'

 

 

Re: Drop a table with unusual name

Hi,

I checked and the name indeed has 125 character, so I believe the "[V_TDAF_PERF_LON_NON_Prime] UNION ALL SELECT distinct [ACCTKEY],[entry_date], [application_id] FROM [V_TDAF_PERF_LOAN_Prime]" is the whole name.

Indeed, if it it the case, then I am not sure why I can't find it using the wholename. How will I incorporate break line if it is the cause of the problem? I also tried dropping it in bteq but it did not wokr.

 

Thank you,

Vi

Teradata Employee

Re: Drop a table with unusual name

Use the Unicode Delimited Identifier syntax (i.e., U&"...") which supports a mixture of escaped hex (for the line break) and characters. Use the char2hexint function to return the hex of the entire name as stored in the dictionary.