Archives of the TeradataForum

Message Posted: Wed, 14 Dec 2005 @ 10:41:58 GMT

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

cbarrineau wrote:

For a single vew/macro the easiest way is "show qualified", but you probably know that already :-)

I once wrote a SP to retrieve all the objects *referencing* a given object, so you just have to invert the logic. But if any of the underlying objects is missing it will not be included.

Looks like a job for a Table UDF in V2R6...

Dieter

     /***
        Returns number of objects (without SPs!) referencing Database_Name.Table_Name.
        Returns -1 if Database_Name.Table_Name object doesn't exist.
        AccessRights on DBC.tvm and DBC.dbase needed.

        WARNINGS:
          - For SPs dbc.tvm.CreateText doesn't have a value -> SPs missing.

          - An object might be missing if dbc.tvm.CreateTxtOverflow = 'C'.
            Hint: That restriction might be removed by checking dbc.TableText.RequestText
            where TextType = 'R'.

          - An object might be erroneously included if an alias with that
            name is used within the query.

          - For reliable information better use Metadata Services instead :-)

        Usage:
          CALL Object_Usage('dbc', 'dbase', Usage_Count);

          SELECT *
          FROM gt_Object_Usage
          ORDER BY level, 1,2;
     ***/

     DROP TABLE gt_Object_Usage;

     CREATE GLOBAL TEMPORARY TABLE gt_Object_Usage(
        Database_Name VARCHAR(30) NOT NULL,
        Table_Name VARCHAR(30) NOT NULL,
        Table_Kind CHAR NOT NULL,
        Level INT NOT NULL
     ) UNIQUE PRIMARY INDEX(Database_Name, Table_Name)
     ON COMMIT PRESERVE ROWS;


     REPLACE PROCEDURE Object_Usage(
        IN DB_Name VARCHAR(30),
        IN Object_Name VARCHAR(30),
        OUT Usage_Count INT
     )
     BEGIN
        DECLARE cnt, lvl INT;

        SELECT -1 INTO :cnt
        WHERE NOT EXISTS
          (SELECT * FROM dbc.Tables
           WHERE DatabaseName = :DB_Name
           AND TableName = :Object_Name);

        IF ACTIVITY_COUNT = 0 THEN

          DELETE FROM gt_Object_Usage;

          SET lvl = 1;

          INSERT INTO gt_Object_Usage
          SELECT
            d.DatabaseName,
            t.TVMName,
            t.TableKind,
            :lvl
          FROM
            DBC.tvm t
              JOIN
            DBC.dbase d
                ON t.DatabaseId = d.DatabaseId
          WHERE
            CreateText LIKE '% "' || :DB_Name || '"."' || :Object_Name || '"%'
          AND
            NOT (d.DatabaseName = :DB_Name AND t.TVMName = :Object_Name)
          AND
            TableKind IN ('V', 'M', 'I', 'G', 'N');

          SET cnt = ACTIVITY_COUNT;

          WHILE ACTIVITY_COUNT > 0 DO

            SET lvl = lvl + 1;

            INSERT INTO gt_Object_Usage
            SELECT
              d.DatabaseName,
              t.TVMName,
              t.TableKind,
              :lvl
            FROM
              DBC.tvm t
                JOIN
              DBC.dbase d
                  ON t.DatabaseId = d.DatabaseId
            WHERE
              CreateText LIKE ANY
               (
                SELECT
                  '% "' || Database_Name || '"."' || Table_Name || '"%'
                FROM gt_Object_Usage
                WHERE level = :lvl - 1
               )
            AND
              TableKind IN ('V', 'M', 'I', 'G', 'N')
            AND
              (d.DatabaseName, t.TVMName) NOT IN
                (SELECT
                   Database_Name, Table_Name
                 FROM gt_Object_Usage);

            SET cnt = cnt + ACTIVITY_COUNT;

          END WHILE;

        END IF;

        SET Usage_Count = cnt;

     END;

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