PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

How to generate the list of database's which are not used by any user in Teradata warehouse ?

Q 34 ) How to generate the list of database’s which are not used by any user in Teradata warehouse ?

Answer >

SELECT
APPNAME
,DATA_UNUSED
,BASEVIEW_UNUSED
,APPVIEW_UNUSED
,WORK_UNUSED
,ARCH_UNUSED
,CNTL_UNUSED
,APPLOGIC_UNUSED
,STAGE_UNUSED
FROM
(
SELECT APPNAME,
MAX(DATA_UNUSED) AS DATA_UNUSED,
MAX(BASEVIEW_UNUSED) AS BASEVIEW_UNUSED,
MAX(APPVIEW_UNUSED) AS APPVIEW_UNUSED,
MAX(WORK_UNUSED) AS WORK_UNUSED,
MAX(ARCH_UNUSED) AS ARCH_UNUSED,
MAX(CNTL_UNUSED) AS CNTL_UNUSED,
MAX(APPLOGIC_UNUSED) AS APPLOGIC_UNUSED,
MAX(STAGE_UNUSED) AS STAGE_UNUSED
FROM
(
SELECT
SUBSTR(DBNME,1,INDEX(DBNME,’_’) -1) AS APPNAME,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS DATA_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxxx’ THEN ‘YES’ ELSE NULL END AS BASEVIEW_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS APPVIEW_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS WORK_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS ARCH_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS CNTL_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS APPLOGIC_UNUSED,
CASE WHEN UPPER(TRIM(DBNME)) LIKE ‘%xxxxx’ THEN ‘YES’ ELSE NULL END AS STAGE_UNUSED
FROM
(
SELECT DISTINCT SUBSTR(DATABASENAME,INDEX(DATABASENAME,’_’)+1) AS DBNME FROM EDW1_UTIL_BASEVIEW.DatabasesV
WHERE DATABASENAME NOT IN ( SELECT DISTINCT ObjectDatabaseName FROM EDW1_UTIL_BASEVIEW.DBQLOBJTBLSUM_HST
WHERE
UserName IN
(
SELECT DatabaseName FROM EDW1_UTIL_BASEVIEW.DBASE
Where RowType = ‘U’
AND ProfileName IN
(
************ LIST Down the various profiles in your environment **********
)
)
AND UserName NOT IN <<<*********** LIST OF USER NAME USED ***********>>>>>
)
AND DATABASENAME LIKE ‘%xxxx%’
AND DBNME LIKE ‘%z_%’ ESCAPE ‘z’
)DT
)DT1
GROUP BY 1
) A
WHERE trim(A.DATA_UNUSED)=’YES’ AND trim(A.BASEVIEW_UNUSED)=’YES’ AND trim(A.APPVIEW_UNUSED)=’YES’ AND trim(A.WORK_UNUSED)=’YES’ AND trim(A.ARCH_UNUSED)=’YES’ AND trim(A.CNTL_UNUSED)=’YES’ AND trim(A.APPLOGIC_UNUSED)=’YES’ AND trim(A.STAGE_UNUSED)=’YES’

Add a Comment

Your email address will not be published. Required fields are marked *