Archives of the TeradataForum

Message Posted: Fri, 30 Mar 2012 @ 22:22:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

If your views and base tables have the same names (reside in different databases), you can probably use something like the following. If they reside in the same database, with different Table/View names, you would need to clone and change this a bit.

     SELECT
         T.Tablename
        ,T.ColumnName
     FROM
        DBC.COLUMNS T
     JOIN
     -Only include tables that exist in both the Base table and View Databases
        DBC.COLUMNS V1
     ON
        T.TableName         = V1.TableName
        AND V1.DatabaseName = YourViewDatabase
     LEFT JOIN
        DBC.COLUMNS V2
     ON
        T.Tablename         = V2.Tablename
        AND T.ColumnName    = V2.ColumnName
        AND V2.databasename = YourViewDatabase
     WHERE
        T.databasename      = YourBaseTableDatabase
     --Only return records where the column doesnt exist in the View database
        AND V2.ColumnName IS NULL;

Johannes de Wet
Unum

	<Prev	Next>		<<First	<Prev	Next>	Last>>