Solved - Repository SQL to Find all Checked out Objects - Informatica

Use below SQL to find All Informatica Checked Out Objects.

SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM HOST_NAME
FROM OPB_TASK O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.TASK_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = O.TASK_TYPE
  AND O.IS_REUSABLE=1
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_SESSION_CONFIG O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.CONFIG_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 72
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_SCHEDULER O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.SCHEDULER_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 69
  AND O.IS_REUSABLE=1
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_MACRO O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.MACRO_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 106
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_SHORTCUT O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.OBJECT_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = O.OBJECT_TYPE
  AND P.OBJECT_TYPE IN (25,
                        1,
                        24,
                        23,
                        44,
                        30,
                        31,
                        21,
                        20,
                        106,
                        69,
                        72,
                        82)
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_MAPPING O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.MAPPING_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 21
  AND O.REF_WIDGET_ID=0
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_MD_DIMENSION O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.DIMENSION_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 31
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_MD_CUBE O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.CUBE_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 30
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_WIDGET O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJECT_ID
  AND P.OBJECT_ID = O.WIDGET_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = O.WIDGET_TYPE
  AND O.IS_REUSABLE=1
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_TARG O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJ_ID
  AND P.OBJECT_ID = O.TARGET_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 2
  AND TBL_TYPE = 0
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE
UNION
SELECT s.SUBJ_NAME,
       P.OBJECT_NAME,
       oot.OBJECT_TYPE_NAME,
       P.LAST_SAVED,
       CAST (P.COMMENTS AS nvarchar(MAX)) COMMENTS,
            U.NAME USER_NAME,
            P.SAVED_FROM
FROM OPB_SRC O,
     OPB_VERSION_PROPS P,
     OPB_SUBJECT S,
     OPB_USER_GROUP U,
     OPB_OBJECT_TYPE oot
WHERE O.IS_VISIBLE = 2
  AND P.SUBJECT_ID = S.SUBJ_ID
  AND P.USER_ID = U.ID
  AND U.TYPE = 1
  AND P.SUBJECT_ID = O.SUBJ_ID
  AND P.OBJECT_ID = O.SRC_ID
  AND P.VERSION_NUMBER = O.VERSION_NUMBER
  AND P.OBJECT_TYPE = 1
  AND oot.OBJECT_TYPE_ID = P.OBJECT_TYPE