Select statements to get metadata

Database
Enthusiast

Select statements to get metadata

Hi all;

We're working on adding a driver to our AutoTag product for Teradata. To do so we need to pull down the metadata from the database. We do all this in C#. Can you please tell me how to:

  • Do you support DbProviderFactory.CreateDataSourceEnumerator()?
  • For a given server, get all databases.
  • Get all tables & views. For each table & view get:
    • schema
    • name
    • description
    • system or user
    • columns in the table/view
  • For each column get:
    • name
    • description
    • type
    • If a foreign key, the schema.table.column of the primary key.
  • For each stored procedure get:
    • schema
    • name
    • description
    • system or user
    • return type
    • parameters
  • For each parameter get:
    • name
    • type
    • data length
    • hasDefault
    • direction
  • How to perform a select with the equivilent of "skip 100, take 50" which returns rows 101 - 150 of a given select.
  • How to return the value of an auto-generated primary key when a row is added to the DB.
  • What syntax does Teradata prefer for parameters in a select (Sql Server wants @param, Oracle wants :param, etc)?
  • A list of all keywords in the SQL syntax.

thanks - dave

Tags (1)
14 REPLIES
Enthusiast

Re: Select statements to get metadata

That is a lot of information for a single post :)

Anyways, Teradata maintains quite a detailed information about its objects (databases, tables, views, columns, datatypes etc). You can start with dbc tables like dbc.database, dbc.tables, dbc.columns etc. All the information will be available there. Most of them are flags, so you probably need to look for them as what they mean (e.g. In dbc.tables - tabletype T defines table, V means View, M means Macro etc.)

There is no concept of schema in terdata.

Like any other RDBMS, the resultset of a query is always random. So skipping first 100 records is not possible. But you can add SAMPLE clause to fetch pure sampled data from the db.

List of keywords, all the ANSI standard keywords should be there plus TD Extension keywords which needs to be googled i guess...

For the auto-generated values, there are two ways to setup "GENERATED ALWAYS" & "GENERATED BY DEFAUT"... Read through the teradata identity columns. But either case, you can't always guarantee the next value system will be using for auto-generated values rather the uniqueness is maintained till the limits are reached depending on the way the columns are defined...

Senior Apprentice

Re: Select statements to get metadata

Seems you use .NET Data Provider. Most of the data should be easily retrieved using the built-in functionality.

The reference is found at: http://developer.teradata.com/doc/connectivity/tdnetdp/14.11/webhelp/webframe.html

A list of restricted words can be found in Appendix B in the "SQL Fundamentals" manual or retrieved from a view: SYSLIB.SQLRESTRICTEDWORDS

Dieter

Enthusiast

Re: Select statements to get metadata

Thank you for the pointers. Does that include the primary key a foreign key points to?

Sorry to bombard for questions but I can't do any queries until we get the sample DB running. We've been trying to get the AWS sample running since Friday, but keep hitting problems (with very obtuse error messages).

Once we get it running, I'll dive into this stuff.

Or, is there a public copy of the sample database I can use so I don't have to wait till we get our copy installed? I only need read access so if there's a read-only copy available somewhere, we could stop fighting the installer of the AWS sample and just use that.

thanks - dave

Senior Apprentice

Re: Select statements to get metadata

Di Dave,

for playing around there's Teradata Express for VMWare:

http://downloads.teradata.com/download/database/teradata-express/vmware

Regarding Foreign Keys you might notice that most customers don't implement them in their DWH, they're just in the logical model and the loading process enforces referential integrity.

Dieter

Enthusiast

Re: Select statements to get metadata

Hi Dieter;

First off, we're trying to install the AWS demo (we don't have a VMWare server, just Hyper-V). We're installing it for the 4th time. Hopefully this time it will work.

Second, how do I get the logical model? Is there a query to get this? We need this PK:FK links to automatically create joins.

thanks - dave

Senior Apprentice

Re: Select statements to get metadata

Hi Dave,

you can probably convert the files from VMWare to Hyper-V, most virtualizers can do that:

http://technet.microsoft.com/en-us/library/hh967435.aspx

The logical model is not stored in the database, it might only be in a data modelling tool like ERwin and/or a huge printout pinned to the wall :-( If you got luck the PK/FK colums share the same name, but even then it's not a reliable way...

Dieter

Enthusiast

Re: Select statements to get metadata

Hi Dieter;

If the PK:FK relationships aren't in the metadata somewhere, how do you enforce them? That's a fundamental attribute of a database being consistent.

??? - thanks - dave

Senior Apprentice

Re: Select statements to get metadata

Hi Dave,

of course a database should have consistent data, but even with PK/FK/CHECKs there might be some bad data in a source system or it's only consistent within a single source system, but you load multiple sources into a DWH. Data quality is a quite important topic, but you might wonder how low this quality is in some DWHs :-)

A table in a warehouse is usually loaded in batches, i.e. hundreds/thousands/millions of rows. When you insert/select into the target table and there's a PK-violation you get an error message and the transaction is rolled back. To avoid this potential rollback you have to write a query filtering those rows violating the PK in the select part. Now you're shure the load will not fail, but when the data is alread checked why should the DBMS do a second check?

Or you load using a MERGE (so you don't have to care about updates v. inserts), now you don't even have to care when you use the logical PK columns to match source/target 

A PK/Unique constraint might also be a huge overhead (if the PK is not implemented as UPI it will be a unique secondary index) and if it's just a logical constraint which is hardly used in WHERE-conditions you can easily avoid that overhead by not implementing it.

Foreign Keys are similar, one row violating it will cause a rollback. Additionally you might have slowy changing dimensions where you keep older versions of the data (an UPDATE inserts a new row and updates the old row's valid_to), but FKs were designed for OLTP system, you can't do table1.column references table2(column) and table1.datecol between table2.valid_from and table2.valid_to, (unless you use Teradata's Temporal feature). And finally you sometimes reload or recreate tables in a warehouse, try that when this table is referenced in a referential constraint :-)

Dieter

Enthusiast

Re: Select statements to get metadata

Hi all;

Still at ground zero on all this. Specifically:

You can start with dbc tables like dbc.database, dbc.tables, dbc.columns etc. All the information will be available there. Most of them are flags, so you probably need to look for them as what they mean (e.g. In dbc.tables - tabletype T defines table, V means View, M means Macro etc.)

Where is this documented?

the resultset of a query is always random. So skipping first 100 records is not possible.

If you do an "order by" then it is not random. We use this for paged results where we have a select and say skip 150, take 50 to get page 4 of the results. All the other SQL vendors have ways to do this (the skip part sometimes being a bit convoluted).

But either case, you can't always guarantee the next value system will be using for auto-generated values 

The other vendors have a command where you can do something like "insert ...; return GENERATED_KEY();" It then returns the generated PK value. Does Teradata have anything like this?

Seems you use .NET Data Provider. Most of the data should be easily retrieved using the built-in functionality.

I went through the docs on this and it does not have any of the metadata info. The ADO.NET spec does not include this, that's why we need the specific selects to return it.

thanks - dave

ps - We are still failing in our attempts to install the sample Teradata DB on AWS so I'm left at present just aple to read docs & web pages but can't actually try anything.