Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2008
04:22 PM

09-18-2008
04:22 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2008
04:50 PM

09-18-2008
04:50 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2008
05:10 PM

09-18-2008
05:10 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2008
05:25 PM

09-18-2008
05:25 PM

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

RADIX OUTPUT HEX

Dieter

RADIX OUTPUT HEX

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2008
09:19 AM

09-19-2008
09:19 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2008
03:48 PM

09-19-2008
03:48 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2008
04:52 PM

09-19-2008
04:52 PM

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

Thanks again for your helpful replies....I'll go with converting the values then.

cheers

-rrenn001

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-21-2008
05:53 PM

12-21-2008
05:53 PM

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,

Thanks,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-22-2008
03:56 AM

12-22-2008
03:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-23-2008
07:23 AM

12-23-2008
07:23 AM

Hi rrenn001,

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

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

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.