Show Table and Data definition

Database
Enthusiast

Show Table and Data definition

We can get the object definition from DBC.TVM table and from other DBC views.Fine! But, if the request text size exceeds the limits set for the field in the dictionary Table,the object definition is truncated at the ends before storing it into dictionary.The other general method to display the object definition is by using SHOW statement.

Now,I want to understand how SHOW command gets the table definition. Does it query Dictionary table to get the definition? if yes, Which table? else, how does Teradata processes this request i.e.,SHOW [OBJECT][OBJECT NAME];

I have same doubt regarding the HELP [OBJECT] [OBJECTNAME];
3 REPLIES
Enthusiast

Re: Show Table and Data definition

Leo
My understanding is that a show object is a reconstruction of the objectdefinition from TVM not the request text which is the users definiton used to create the object

Hope this helps

davidpracy
Enthusiast

Re: Show Table and Data definition

David,

If it is reconstructing from Dictionary, then probably it might also query other tables like DBC.TVFields and DBC.Indexes too.

I did not find any clues in any documentation regarding how SHOW and HELP works.Can you suggest any document that has these details?
rgs
Enthusiast

Re: Show Table and Data definition

The dictionary tables contain all object definitions defined on the database. A SHOW or HELP statement is just like any other SQL statement such as a SELECT for example. It causes the Par$er to read the appropriate dictionary tables to figure out what to do with the SQL statement. In the case of a SHOW it will just builds the output text for the requested object. For a SELECT it will acquire all of the information from the dictionary tables (if not already cached) to build the request, optimize it and build the steps necessary to execute the request. For a SHOW TABLE it will need to access the dictionary tables you mentioned. For a SHOW FUNCTION it will need to access those and some others. So it depends on the type of object the SHOW is for.