Archives of the TeradataForum

Message Posted: Tue, 18 Jul 2006 @ 16:37:10 GMT

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

Seeing that you are attempting to do what is basically an admin function, a stored procedure is a perfect solution.

Steps:

     1.  ct columntable (dbname varchar(30), tblname varchar(30), rowcount
     integer) unique primary index (dbname, tblname)

     2.  replace procedure colfinder (IN colname varchar(30), IN val2find
     integer)
         begin
         declare cnt integer;
         for curptr as myloop
           cursor for select trim(databasename) as db,trim(tablename) as tbl
     from dbc.columns where columnname=:colname
         do
            call dbc.sysexecsql('insert into columntable  select
     '''||curptr.db||''' , '''||curptr.tbl||''',count(*)from '
                  ||curptr.db||'.'||curptr.tbl||' where '||colname||' =
     '||val2find||';');
            end for;
     end;

     3.  call colfinder ('dept_no',200)

This will bring back any table name in any database and ins both names into the columntable along with the number rows in the table that contain the value passed to it. This will also include tablenames with zero rows in the table. If this is not what you want, you need to add a HAVING. Cause on copying and pasting this SP. when you see '' or ''' they are not double quotes. They are multiple single quotes in order to obtain a literal single quote in the SQL statement.

Of course you can rename the tables and SP to any name you prefer.

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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