BackUp Identification Process.

General
Enthusiast

BackUp Identification Process.

Fellows,

I am working on a process to identfy backups within environment.

And in order to answer this I designed a query below query but this only answers that what all tables within have identical structure.

But does not answers the big one.

So is any further additional filter criteria can be introduce to answer the real question.

(I know this question is weired because of involvement of many parameter like inconsistent nomenclature, creation timestamps etc., But still wold like to take feed back from experts.)

************************************************************************************************

SELECT

B.DATABASENAME,

B.TABLENAME,

B.COL_CNT,

C.DATABASENAME,

C.TABLENAME,

C.COL_CNT

FROM

(SELECT

DATABASENAME DATABASENAME,

TABLENAME TABLENAME,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS COLUMNNAME,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS COLUMNTYPE,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS NULLABLE,

COUNT(COLUMNNAME) COL_CNT

FROM

(SELECT 

COL.DATABASENAME DATABASENAME,

COL.TABLENAME TABLENAME,

COL.COLUMNNAME COLUMNNAME,

COL.COLUMNTYPE COLUMNTYPE,

COL.COLUMNLENGTH COLUMNLENGTH,

COL.NULLABLE NULLABLE,

RANK() OVER (PARTITION BY COL.DATABASENAME,COL.TABLENAME ORDER BY COL.COLUMNID) COLUMNPOSITION 

--RANK(DATABASENAME,TABLENAME,COLUMNID) RNK_COLUMNID

FROM

DBC.COLUMNS COL JOIN DBC.TABLES TAB ON COL.DATABASENAME = TAB.DATABASENAME AND COL.TABLENAME=TAB.TABLENAME AND TABLEKIND = 'T') A

GROUP BY 1,2 ) B

JOIN

(SELECT

DATABASENAME DATABASENAME,

TABLENAME TABLENAME,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(COLUMNNAME) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS COLUMNNAME,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(COLUMNTYPE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS COLUMNTYPE,

MAX(CASE WHEN COLUMNPOSITION = 1 THEN TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 2 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 3 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 4 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 5 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 6 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 7 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 8 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 9 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 10 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 11 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 12 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 13 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 14 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 15 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 16 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 17 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 18 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 19 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 20 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 21 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 22 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 23 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 24 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 25 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 26 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 27 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 28 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 29 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION = 30 THEN ',' || TRIM(NULLABLE) ELSE '' END) ||

MAX(CASE WHEN COLUMNPOSITION > 16  THEN ',...' ELSE '' END) AS NULLABLE,

COUNT(COLUMNNAME) COL_CNT

FROM

(SELECT 

COL.DATABASENAME DATABASENAME,

COL.TABLENAME TABLENAME,

COL.COLUMNNAME COLUMNNAME,

COL.COLUMNTYPE COLUMNTYPE,

COL.COLUMNLENGTH COLUMNLENGTH,

COL.NULLABLE NULLABLE,

RANK() OVER (PARTITION BY COL.DATABASENAME,COL.TABLENAME ORDER BY COL.COLUMNID) COLUMNPOSITION 

--RANK(DATABASENAME,TABLENAME,COLUMNID) RNK_COLUMNID

FROM

DBC.COLUMNS COL JOIN DBC.TABLES TAB ON COL.DATABASENAME = TAB.DATABASENAME AND COL.TABLENAME=TAB.TABLENAME AND TABLEKIND = 'T') A

GROUP BY 1,2 ) C

ON B.COLUMNNAME = C.COLUMNNAME

AND B.COLUMNTYPE= C.COLUMNTYPE

AND B.NULLABLE= C.NULLABLE

AND B.COL_CNT= C.COL_CNT

AND (B.DATABASENAME <> C.DATABASENAME AND B.TABLENAME<> C.TABLENAME)

************************************************************************************************

Regards,

Subhash

Tags (1)