Linking a Table name to a table Id

Database
Enthusiast

Linking a Table name to a table Id

Hello,

Are there any system tables, views that will correlate tables name to its table id?
I have tried looking in dbc.tables,dbc.columns with no luck.
Thanks for your help.
10 REPLIES
Junior Contributor

Re: Linking a Table name to a table Id

SELECT * FROM dbc.COLUMNS
WHERE columnname IN ('tvmid', 'tableid')

You have to check if you got select rights on any of those rows returned, because most of them are from base tables.

Dieter
Enthusiast

Re: Linking a Table name to a table Id

The reason I am asking is because in ferret, the tableid's are in decimal. I am not trying to do conversions to get the tables/ids matched up from hex to decimal.

select tablename, tableid from dbc.tables3vx;

The result would look like this...

tablename1 tableid1

tablename2 tableid2

BUT the tableid's are in HEX.

Any suggestions in how to match the hex tableid to decimal tableid to get the table name in ferret?
Junior Contributor

Re: Linking a Table name to a table Id

You can change Ferret's default output format using the RADIX command:
RADIX OUTPUT HEX

Dieter
Enthusiast

Re: Linking a Table name to a table Id

Dieter,

Thank you for your help, that would have seemed to do the trick, however its not actually producing results in hex format, it still outputs in decimal.

cmd -> radix
input in decimal
output in decimal

cmd->radix output hex
input in decimal
output in hex

cmd->showblocks
tableid shows id's in decimal even though output is set to hex.

Is there another way to output the command showblocks with tableid's in hex?

Thanks again.
Junior Contributor

Re: Linking a Table name to a table Id

I don't know if it's possible to change showblocks output, but you can change the hex tableid to a decimal:

HASHBUCKET(SUBSTRING(tableid FROM 2 FOR 1) || SUBSTRING(tableid FROM 1 FOR 1) (BYTE(4)))
/ ((HASHBUCKET()+1)/65536),
HASHBUCKET(SUBSTRING(tableid FROM 4 FOR 1) || SUBSTRING(tableid FROM 3 FOR 1) (BYTE(4)))
/ ((HASHBUCKET()+1)/65536)

Dieter
Enthusiast

Re: Linking a Table name to a table Id

I was guessing you would say that was the next option.
Thanks again for your helpful replies....I'll go with converting the values then.
cheers
-rrenn001
N/A

Re: Linking a Table name to a table Id

Hey Dieter, I'm trying to figure out why the HashBucket routine has the divide by 1. Is that because Hashbucket() will return a value greater than 65535 in V12 and >?

Thanks,
Junior Contributor

Re: Linking a Table name to a table Id

Hi Kent,
you're right, it's because since TD12 a system might be set up to use a larger hashmap, 2^20 instead of 2^16.
Hashmap() returns the maximum hash bucket, 65.535 or 1.048.575, thus the calculation yields 1 or 16.

Dieter
Enthusiast

Re: Linking a Table name to a table Id

Hi rrenn001,

U could find the link between TABLE/VIEW/MACRO and its respective TVM ID in the view dbc.tables2