|
General | Welcome to the Teradata Forums.
Read the
guidelines on posting.
Email notifications and RSS feeds are available, and also a chronological list of all forum activity. |
|
|
Active Topics Last 60 days
|
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