Archives of the TeradataForum

Message Posted: Mon, 08 Nov 2004 @ 20:18:08 GMT


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

Subj:		Re: Dependencies Query

From:		Dieter Noeth

Anomy Anom wrote:

Does anyone know of a query in which I can check for views that are dependant on a certain table? I would like to drop a table but before I do I want to know if there are any views out there that might be affected by doing so.

I wrote a SP for that purpose, which returns all objects (ncluding nested objects) referring to a given table/view.

I hope i attached the right one, because i can't test it right now...

Dieter

     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 Database_Name VARCHAR(30),
        IN Object_Name VARCHAR(30),
        OUT Usage_Count INT
     )
     BEGIN
        DECLARE cnt, lvl INT;

        SET lvl = 1;

        DELETE FROM gt_Object_Usage;

        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 '% "' || :Database_Name || '"."' || :Object_Name ||'"%';

        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
            (d.DatabaseName, t.TVMName) NOT IN
              (SELECT
                 Database_Name, Table_Name
                 FROM gt_Object_Usage)
          AND
            CreateText LIKE ANY
             (
              SELECT
                '% "' || Database_Name || '"."' || table_name || '"%'
              FROM gt_Object_Usage
              WHERE level = :lvl - 1
             );

          SET cnt = cnt + ACTIVITY_COUNT;

        END WHILE;

        SET Usage_Count = cnt;

     END;


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

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


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

Attachments

Library

Quick Reference