I have one question regarding your original request:
do the names of the columns matter, or do tables qualify as "equal"
if only the datatypes match, but not the column names?
Anyway:
I would try to solve this problem in two steps:
first, I would only compare tables that have the same colcount;
IIRC, the colcount is recorded in SYSTABLES, so this is easy to do.
second, I would try to construct a temp table (or a file) containing
the table name and qualifier and one long varchar which contains all the column information
in the right order, that is, column name, data type, length, scale, null and default info,
in a compact representation, for every column (I hope that it will be sufficient to
truncate the column names to the first 18 chars ...)
so the information for one column is only ca. 30 bytes, if you do it as compact as possible;
that allows for 1000 columns in a 32k long varchar, which should be sufficient.
Don't know, if it will work with a pure SQL solution; maybe I would use some REXX,
PL/1 or C for this task.
Kind regards
Bernd
Am 03.01.2015 um 16:10 schrieb M.P.:
>
> I am trying to identify the duplicate tables having exactly same
> columns who are hiding under a different name and perform clean up (if
> possible).
>
> Regards,
>
> Manoj
>
>
> -----End Original Message-----
|