Oppolzer - Informatik / Blog


Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie

DB2-L - Tabellen mit gleicher Struktur finden

Subject:

Re: DB2 10 for z/OS: SQL assistance

From:

Bernd Oppolzer <bernd.oppolzer@T-ONLINE.DE>

Reply-To:

DB2 List <db2-l@lists.idug.org>

Date:

2015.01.04 02:00:00


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-----

Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie