Table exists and shows in dbc.tablesize but not showing in dbc.tables

General
Enthusiast

Table exists and shows in dbc.tablesize but not showing in dbc.tables

I had a scenario where I couldn't find some tables in dbc.tables but could find the same via dbc.tablesize.

I was looking for tables with comma ',' added to the table name: I couldn’t find the tables using the below query

sel * from dbc.tables where databasename=<dbname> and tablename like'%,%'

but could see the same via sel tablename from dbc.tablesize where databasename=<dbname> and tablename like '%,'

Any ideas how these tables can get created? Is this related to some header issue? Why it’s not showing in dbc.tables?

Any help on this is much appreciated.

Thanks,

Dilin

6 REPLIES
Senior Apprentice

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

Did you try a SHOW TABLE?

Are you shure it's a table?

Stored Procedures and UDFs also show up in dbc.TableSize.

Dieter

WAQ
Enthusiast

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

I was looking for tables with comma ',' added to the table name: I couldn’t find the tables using the below query

sel * from dbc.tables where databasename=<dbname> and tablename like'%,%'

Are sure your table name contains comma. Are we allowed to use commas in the table names?

Enthusiast

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

Thanks Dieter, WAQ

I just found the issue:  the tables were created by ETL jobs - they created tables with "¸" in the name which is not the comma (",") that we use typically; I couldn't find the exact Unicode name for  "¸" .

Below pasted a sample table name that I was referring.

 "PREPAY_CODE¸¸¸¸¸"

Thanks for looking into this.

Hi WAQ,

We can create tables with comma in the name, but is not recommended.

Thanks,

Dilin

WAQ
Enthusiast

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

I don't think that comma is allowed in the table names.

I tested the following queries on Teradata 12 and the first CREATE query didn't execute while the later worked having the only difference of "," and "_' between the two

drop table test_tbl;

create table test,tbl

(

val varchar(10)

);

create table test_tbl

(

val varchar(10)

);

Senior Apprentice

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

You need to use double quotes:

create table "test,tbl"

(

val varchar(10)

);

Thus you can create any kind of studid table name :-)

The worst i've ever seen was a tablename with included double quotes like this:

create table """test,tbl"""

(

val varchar(10)

);

Dieter

Enthusiast

Re: Table exists and shows in dbc.tablesize but not showing in dbc.tables

encapsulate the table name in quotes... It will work

Create table "test,tbl" ...