Databases
StackOverflow2013
Postsdbo
POTeradata: Is there a way to generate DDL from a view or select statement?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTeradata: Is there a way to generate DDL from a view or select statement?
    primarykey
    data
    text
    <p>I am using a global application user account to access database A. This user account does not have permissions to modify database A's schema (ie, create tables, modify tables, etc). This user also has access to database B, but only views. I need to run SQL to feed data from a view in database B into a table in database A.</p> <p>In a perfect world, I would be able to use this SQL:</p> <p><code>create database_a.mytable as (select * from database_b) with no data</code></p> <p>However, the user can't create tables in database A. If I could get the DDL of the select statement then I could log in under my personal account (which doesn't have any access to database B) and run the DDL in database A to create the table.</p> <p>The only other option is to manually write the SQL, but I don't want to do that, especially since this view I am wanting to copy has many columns of varying data types and sizes.</p> <p><strong>Edit:</strong> I may be getting closer. I just experimented with this:</p> <p><code>show (select * from database_b.myview)</code></p> <p>However, it generated the DLL of every single table that is used in the view itself, as well as the definition for the view. This doesn't really help me since I just want the schema of the select statement itself. In other words, I need what would be generated if I were to use the <code>create table as</code> statement mentioned above.</p> <p><strong>Edit for Rob:</strong> Perhaps "DDL" was the wrong term to use. Using <code>show view db.myview</code> just shows the definition of the view, not the schema it represents. In my above example of <code>create table as</code>, I show how you can create a table that mimics the schema of a result set returned in a select. It generates a DDL on the back end for creating a table and then executes that DDL to actually create the table. You can then say <code>show table db.newtable</code> and see the new table's DDL. I want to get <em>that</em> DDL directly from a select statement so that I can copy it, log out of the app account, into my personal account, and then execute the DDL to create the table.</p> <p>This is only to save me the headache of having to type out the DDL manually by hand to save time and reduce typing errors, especially since the source view has so many columns. That said, I think hitting up the DBA or writing some snazzy stored procedure to do dynamic stuff would be a bit over the top for my needs. I think there has to be a way to get the DDL for creating a table schema directly from a select statement.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USoscilatingcretin
    1. USoscilatingcretin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTeradata: Is there a way to generate DDL from a view or select statement?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTeradata: Is there a way to generate DDL from a view or select statement?
      1. USlexu
      1. VTFavorite
    3. VO
      singulars
      1. POTeradata: Is there a way to generate DDL from a view or select statement?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload