Extraction Queries for SAP IQ¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Schema¶
Ensure your query has columns labelled as CATALOG and SCHEMA in the select list.
SELECT
TRIM(USER_NAME) AS 'SCHEMA',
null AS 'CATALOG'
FROM SYSUSER
WHERE USER_NAME NOT IN ('''')
AND USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' , 'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' , 'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' , 'SYS_ALTER_ANY_SEQUENCE_ROLE' ,
'SYS_ALTER_ANY_TABLE_ROLE' , 'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' , 'SYS_AUTH_OPERATOR_ROLE' ,
'SYS_AUTH_PERMS_ADMIN_ROLE' , 'SYS_AUTH_PROFILE_ROLE' ,
'SYS_AUTH_READCLIENTFILE_ROLE' , 'SYS_AUTH_READFILE_ROLE' ,
'SYS_AUTH_RESOURCE_ROLE' , 'SYS_AUTH_SA_ROLE' ,
'SYS_AUTH_SPACE_ADMIN_ROLE' , 'SYS_AUTH_SSO_ROLE' ,
'SYS_AUTH_USER_ADMIN_ROLE' , 'SYS_AUTH_VALIDATE_ROLE' ,
'SYS_AUTH_WRITECLIENTFILE_ROLE' , 'SYS_AUTH_WRITEFILE_ROLE' ,
'SYS_BACKUP_DATABASE_ROLE' , 'SYS_CHANGE_PASSWORD_ROLE' ,
'SYS_CHECKPOINT_ROLE' , 'SYS_COMMENT_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' , 'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' ,
'SYS_CREATE_ANY_VIEW_ROLE' , 'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' , 'SYS_CREATE_VIEW_ROLE' ,
'SYS_DEBUG_ANY_PROCEDURE_ROLE' , 'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' , 'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' , 'SYS_DROP_ANY_PROCEDURE_ROLE' ,
'SYS_DROP_ANY_SEQUENCE_ROLE' , 'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' , 'SYS_DROP_ANY_VIEW_ROLE' ,
'SYS_DROP_CONNECTION_ROLE' , 'SYS_DROP_DATATYPE_ROLE' ,
'SYS_DROP_MESSAGE_ROLE' , 'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' , 'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' ,
'SYS_MANAGE_ANY_USER_ROLE' , 'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' , 'SYS_MANAGE_PROFILING_ROLE' ,
'SYS_MANAGE_REPLICATION_ROLE' , 'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' ,
'SYS_REORGANIZE_ANY_OBJECT_ROLE' , 'SYS_REPLICATION_ADMIN_ROLE' ,
'SYS_RUN_REPLICATION_ROLE' , 'SYS_SELECT_ANY_TABLE_ROLE' ,
'SYS_SEND_EMAIL_ROLE' , 'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' , 'SYS_UPDATE_ANY_TABLE_ROLE' ,
'SYS_UPGRADE_ROLE_ROLE' , 'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' , 'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
ORDER BY
'CATALOG'
'SCHEMA';
Table¶
Ensure your query has columns labelled as CATALOG, SCHEMA, TABLE_NAME, TABLE_OWNER, TABLE_TYPE, REMARKS in the SELECT list.
SELECT
null AS CATALOG,
TRIM(U.USER_NAME) AS SCHEMA,
TRIM(TABLE_NAME) AS TABLE_NAME,
CASE WHEN TABLE_TYPE = 'BASE' THEN 'TABLE'
ELSE 'VIEW'
END AS 'TABLE_TYPE',
TRIM(T.REMARKS) AS REMARKS,
TRIM(U.USER_NAME) AS TABLE_OWNER
FROM SYS.SYSTABLE T
INNER JOIN SYSUSER U
ON T.CREATOR = U.USER_ID
WHERE USER_NAME NOT IN ('''')
AND USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' , 'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' , 'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' , 'SYS_ALTER_ANY_SEQUENCE_ROLE' ,
'SYS_ALTER_ANY_TABLE_ROLE' , 'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' , 'SYS_AUTH_OPERATOR_ROLE' ,
'SYS_AUTH_PERMS_ADMIN_ROLE' , 'SYS_AUTH_PROFILE_ROLE' ,
'SYS_AUTH_READCLIENTFILE_ROLE' , 'SYS_AUTH_READFILE_ROLE' ,
'SYS_AUTH_RESOURCE_ROLE' , 'SYS_AUTH_SA_ROLE' ,
'SYS_AUTH_SPACE_ADMIN_ROLE' , 'SYS_AUTH_SSO_ROLE' ,
'SYS_AUTH_USER_ADMIN_ROLE' , 'SYS_AUTH_VALIDATE_ROLE' ,
'SYS_AUTH_WRITECLIENTFILE_ROLE' , 'SYS_AUTH_WRITEFILE_ROLE' ,
'SYS_BACKUP_DATABASE_ROLE' , 'SYS_CHANGE_PASSWORD_ROLE' ,
'SYS_CHECKPOINT_ROLE' , 'SYS_COMMENT_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' , 'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' , 'SYS_CREATE_ANY_VIEW_ROLE' ,
'SYS_CREATE_DATATYPE_ROLE' , 'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' , 'SYS_CREATE_VIEW_ROLE' ,
'SYS_DEBUG_ANY_PROCEDURE_ROLE' , 'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' , 'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' , 'SYS_DROP_ANY_PROCEDURE_ROLE' ,
'SYS_DROP_ANY_SEQUENCE_ROLE' , 'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' , 'SYS_DROP_ANY_VIEW_ROLE' ,
'SYS_DROP_CONNECTION_ROLE' , 'SYS_DROP_DATATYPE_ROLE' ,
'SYS_DROP_MESSAGE_ROLE' , 'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' , 'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' , 'SYS_MANAGE_PROFILING_ROLE' ,
'SYS_MANAGE_REPLICATION_ROLE' , 'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' , 'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' , 'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' , 'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' , 'SYS_SET_USER_ROLE' ,
'SYS_SPATIAL_ADMIN_ROLE' , 'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' , 'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' , 'SYS_VALIDATE_ANY_OBJECT_ROLE' ,
'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
AND T.TABLE_TYPE IN ('BASE')
ORDER BY
TABLE_TYPE,
CATALOG,
SCHEMA,
TABLE_NAME;
View¶
Ensure your query has columns labelled as CATALOG, SCHEMA, VIEW_NAME, VIEW_CREATE_STATEMENT, TABLE_OWNER, 'VIEW' AS VIEW_TYPE (this is a hard coded column), REMARKS in the SELECT list.
SELECT
null AS CATALOG,
TRIM(U.USER_NAME) AS SCHEMA,
TRIM(T.TABLE_NAME) AS VIEW_NAME,
TRIM(T.VIEW_DEF) AS VIEW_CREATE_STATEMENT,
TRIM(U.USER_NAME) AS TABLE_OWNER,
'VIEW' AS 'VIEW_TYPE',
'' AS 'REMARKS'
FROM SYS.SYSTABLE T
INNER JOIN SYSUSERPERMS U
ON T.CREATOR = U.USER_ID
WHERE USER_NAME NOT IN ('''')
AND USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' ,
'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' ,
'SYS_ALTER_ANY_OBJECT_ROLE' , 'SYS_ALTER_ANY_PROCEDURE_ROLE' ,
'SYS_ALTER_ANY_SEQUENCE_ROLE' , 'SYS_ALTER_ANY_TABLE_ROLE' ,
'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' ,
'SYS_ALTER_ANY_VIEW_ROLE' , 'SYS_ALTER_DATABASE_ROLE' ,
'SYS_ALTER_DATATYPE_ROLE' , 'SYS_AUTH_BACKUP_ROLE' ,
'SYS_AUTH_DBA_ROLE' , 'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' ,
'SYS_AUTH_OPERATOR_ROLE' , 'SYS_AUTH_PERMS_ADMIN_ROLE' ,
'SYS_AUTH_PROFILE_ROLE' , 'SYS_AUTH_READCLIENTFILE_ROLE' ,
'SYS_AUTH_READFILE_ROLE' , 'SYS_AUTH_RESOURCE_ROLE' ,
'SYS_AUTH_SA_ROLE' , 'SYS_AUTH_SPACE_ADMIN_ROLE' ,
'SYS_AUTH_SSO_ROLE' , 'SYS_AUTH_USER_ADMIN_ROLE' ,
'SYS_AUTH_VALIDATE_ROLE' , 'SYS_AUTH_WRITECLIENTFILE_ROLE' ,
'SYS_AUTH_WRITEFILE_ROLE' , 'SYS_BACKUP_DATABASE_ROLE' ,
'SYS_CHANGE_PASSWORD_ROLE' , 'SYS_CHECKPOINT_ROLE' ,
'SYS_COMMENT_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' ,
'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' , 'SYS_CREATE_ANY_VIEW_ROLE' ,
'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' , 'SYS_CREATE_VIEW_ROLE' ,
'SYS_DEBUG_ANY_PROCEDURE_ROLE' , 'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' , 'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' , 'SYS_DROP_ANY_PROCEDURE_ROLE' ,
'SYS_DROP_ANY_SEQUENCE_ROLE' , 'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_DROP_ANY_VIEW_ROLE' , 'SYS_DROP_CONNECTION_ROLE' ,
'SYS_DROP_DATATYPE_ROLE' , 'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' , 'SYS_MANAGE_PROFILING_ROLE' ,
'SYS_MANAGE_REPLICATION_ROLE' , 'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' , 'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' , 'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' , 'SYS_UPDATE_ANY_TABLE_ROLE' ,
'SYS_UPGRADE_ROLE_ROLE' , 'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' , 'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
AND T.TABLE_TYPE = 'VIEW'
ORDER BY
SCHEMA,
VIEW_NAME;
Column¶
Ensure your query has columns labelled as CATALOG, SCHEMA, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, COLUMN_DEFAULT, LENGTH, SCALE in the SELECT list.
SELECT
null AS CATALOG,
TRIM(U.USER_NAME) AS SCHEMA,
TRIM(C.TNAME) AS TABLE_NAME,
TRIM(C.CNAME) AS COLUMN_NAME,
TRIM(C.COLNO) AS ORDINAL_POSITION,
TRIM(DEFAULT_VALUE) AS COLUMN_DEFAULT,
(CASE WHEN TRIM(C.COLTYPE) = 'varchar' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'bigint' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'bit' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'decimal' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||','||C.SYSLENGTH ||')')
WHEN TRIM(C.COLTYPE) = 'double' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||','||C.SYSLENGTH ||')')
WHEN TRIM(C.COLTYPE) = 'integer' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'numeric' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||','||C.SYSLENGTH ||')')
WHEN TRIM(C.COLTYPE) = 'float' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||','||C.SYSLENGTH ||')')
WHEN TRIM(C.COLTYPE) = 'smallint' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'tinyint' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'uniqueidentifier' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'char' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'date' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'timestamp' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'time' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'binary' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'varbinary' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
WHEN TRIM(C.COLTYPE) = 'varchar' THEN TRIM(C.COLTYPE) || ('('||C.LENGTH||')')
ELSE TRIM(TRIM(C.COLTYPE))
END ) AS TYPE_NAME,
TRIM(C.REMARKS) AS REMARKS, TRIM(TRIM(C.COLTYPE)) AS DATA_TYPE,
TRIM(C.LENGTH) AS LENGTH,
TRIM(C.SYSLENGTH) AS SCALE,
TRIM(C.NULLS) AS IS_NULLABLE
FROM SYS.SYSCOLUMNS AS C
INNER JOIN SYSUSERPERMS U
ON C.CREATOR = U.USER_NAME
INNER JOIN SYS.SYSTABLE T
ON U.USER_ID = T.CREATOR
AND C.TNAME = T.TABLE_NAME
WHERE USER_NAME NOT IN ('''')
AND USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' ,
'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' ,
'SYS_ALTER_ANY_OBJECT_ROLE' , 'SYS_ALTER_ANY_PROCEDURE_ROLE' ,
'SYS_ALTER_ANY_SEQUENCE_ROLE' , 'SYS_ALTER_ANY_TABLE_ROLE' ,
'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' , 'SYS_AUTH_OPERATOR_ROLE' ,
'SYS_AUTH_PERMS_ADMIN_ROLE' , 'SYS_AUTH_PROFILE_ROLE' ,
'SYS_AUTH_READCLIENTFILE_ROLE' , 'SYS_AUTH_READFILE_ROLE' ,
'SYS_AUTH_RESOURCE_ROLE' , 'SYS_AUTH_SA_ROLE' ,
'SYS_AUTH_SPACE_ADMIN_ROLE' , 'SYS_AUTH_SSO_ROLE' ,
'SYS_AUTH_USER_ADMIN_ROLE' , 'SYS_AUTH_VALIDATE_ROLE' ,
'SYS_AUTH_WRITECLIENTFILE_ROLE' , 'SYS_AUTH_WRITEFILE_ROLE' ,
'SYS_BACKUP_DATABASE_ROLE' , 'SYS_CHANGE_PASSWORD_ROLE' ,
'SYS_CHECKPOINT_ROLE' , 'SYS_COMMENT_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' , 'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' , 'SYS_CREATE_ANY_VIEW_ROLE' ,
'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' ,
'SYS_CREATE_TABLE_ROLE' , 'SYS_CREATE_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_VIEW_ROLE' ,
'SYS_DEBUG_ANY_PROCEDURE_ROLE' , 'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' ,
'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' ,
'SYS_DROP_ANY_PROCEDURE_ROLE' , 'SYS_DROP_ANY_SEQUENCE_ROLE' ,
'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' , 'SYS_DROP_ANY_VIEW_ROLE' ,
'SYS_DROP_CONNECTION_ROLE' ,
'SYS_DROP_DATATYPE_ROLE' , 'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' , 'SYS_MANAGE_PROFILING_ROLE' ,
'SYS_MANAGE_REPLICATION_ROLE' , 'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' ,
'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' , 'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' , 'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
AND T.TABLE_TYPE IN ('BASE' , 'VIEW')
ORDER BY
CATALOG,
SCHEMA,
TABLE_NAME,
ORDINAL_POSITION;
Primary Key¶
Ensure your query has columns labelled as CATALOG, SCHEMA, TABLE_NAME, PK_NAME, COLUMN_NAME, INDEXTYPE in the SELECT list.
SELECT null AS CATALOG,
TRIM(i.icreator) AS SCHEMA,
TRIM(i.tname) AS TABLE_NAME,
TRIM(i.iname) AS pk_name,
TRIM(c.cname) AS COLUMN_NAME,
i.indextype
FROM sys.sysindexes i
INNER JOIN sysuserperms u
ON i.icreator = u.user_name
AND i.indextype = 'Primary Key'
INNER JOIN sys.systable t
ON u.user_id = t.creator
INNER JOIN sys.syscolumns c
ON c.tname=i.tname
AND c.in_primary_key='Y'
WHERE USER_NAME NOT IN ('''')
AND USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' ,
'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' ,
'SYS_ALTER_ANY_OBJECT_ROLE' , 'SYS_ALTER_ANY_PROCEDURE_ROLE' ,
'SYS_ALTER_ANY_SEQUENCE_ROLE' , 'SYS_ALTER_ANY_TABLE_ROLE' ,
'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' ,
'SYS_ALTER_ANY_VIEW_ROLE' , 'SYS_ALTER_DATABASE_ROLE' ,
'SYS_ALTER_DATATYPE_ROLE' , 'SYS_AUTH_BACKUP_ROLE' ,
'SYS_AUTH_DBA_ROLE' , 'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' ,
'SYS_AUTH_OPERATOR_ROLE' , 'SYS_AUTH_PERMS_ADMIN_ROLE' ,
'SYS_AUTH_PROFILE_ROLE' , 'SYS_AUTH_READCLIENTFILE_ROLE' ,
'SYS_AUTH_READFILE_ROLE' , 'SYS_AUTH_RESOURCE_ROLE' ,
'SYS_AUTH_SA_ROLE' , 'SYS_AUTH_SPACE_ADMIN_ROLE' ,
'SYS_AUTH_SSO_ROLE' , 'SYS_AUTH_USER_ADMIN_ROLE' ,
'SYS_AUTH_VALIDATE_ROLE' , 'SYS_AUTH_WRITECLIENTFILE_ROLE' ,
'SYS_AUTH_WRITEFILE_ROLE' , 'SYS_BACKUP_DATABASE_ROLE' ,
'SYS_CHANGE_PASSWORD_ROLE' , 'SYS_CHECKPOINT_ROLE' ,
'SYS_COMMENT_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' , 'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' ,
'SYS_CREATE_ANY_VIEW_ROLE' , 'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_VIEW_ROLE' , 'SYS_DEBUG_ANY_PROCEDURE_ROLE' ,
'SYS_DELETE_ANY_TABLE_ROLE' , 'SYS_DROP_ANY_INDEX_ROLE' ,
'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' ,
'SYS_DROP_ANY_PROCEDURE_ROLE' , 'SYS_DROP_ANY_SEQUENCE_ROLE' ,
'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_DROP_ANY_VIEW_ROLE' , 'SYS_DROP_CONNECTION_ROLE' ,
'SYS_DROP_DATATYPE_ROLE' , 'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' , 'SYS_MANAGE_AUDITING_ROLE' ,
'SYS_MANAGE_CERTIFICATES_ROLE' , 'SYS_MANAGE_MULTIPLEX_ROLE' ,
'SYS_MANAGE_PROFILING_ROLE' , 'SYS_MANAGE_REPLICATION_ROLE' ,
'SYS_MANAGE_ROLES_ROLE' , 'SYS_MONITOR_ROLE' ,
'SYS_READ_CLIENT_FILE_ROLE' , 'SYS_READ_FILE_ROLE' ,
'SYS_REORGANIZE_ANY_OBJECT_ROLE' , 'SYS_REPLICATION_ADMIN_ROLE' ,
'SYS_RUN_REPLICATION_ROLE' , 'SYS_SELECT_ANY_TABLE_ROLE' ,
'SYS_SEND_EMAIL_ROLE' , 'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' , 'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' , 'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
ORDER BY
SCHEMA,
TABLE_NAME,
PK_NAME;
Foreign Key¶
Ensure your query has columns labelled as FK_CATALOG, FK_SCHEMA, FK_TABLE, FK_NAME, FK_COLUMN, PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_NAME, PK_COLUMN in the SELECT list.
SELECT null AS FK_CATALOG,
TRIM(I.ICREATOR) AS FK_SCHEMA,
TRIM(I.TNAME) AS FK_TABLE,
TRIM(I.INAME) AS FK_NAME ,
TRIM(REPLACE(TRIM(I.colnames),'ASC','')) AS FK_Column ,
null AS PK_CATALOG,
TRIM(PKI.ICREATOR) AS PK_SCHEMA,
TRIM(PKI.TNAME) AS PK_TABLE,
TRIM(PKI.INAME) AS PK_NAME ,
TRIM(REPLACE(TRIM(PKI.colnames),'ASC','')) AS PK_Column
FROM SYS.SYSINDEXES I
INNER JOIN SYS.SYSINDEXES PKI
ON PKI.INAME = I.INAME
AND PKI.INDEXTYPE = 'Primary Key'
AND I.INDEXTYPE = 'Foreign Key'
INNER JOIN SYSUSERPERMS U
ON I.ICREATOR = U.USER_NAME
INNER JOIN SYS.SYSTABLE T
ON U.USER_ID = T.CREATOR
AND I.TNAME = T.TABLE_NAME
AND T.TABLE_TYPE IN ('BASE' , 'VIEW')
WHERE U.USER_NAME NOT IN ('''')
AND U.USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' , 'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' , 'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' , 'SYS_ALTER_ANY_SEQUENCE_ROLE' ,
'SYS_ALTER_ANY_TABLE_ROLE' , 'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' , 'SYS_AUTH_OPERATOR_ROLE' ,
'SYS_AUTH_PERMS_ADMIN_ROLE' , 'SYS_AUTH_PROFILE_ROLE' ,
'SYS_AUTH_READCLIENTFILE_ROLE' , 'SYS_AUTH_READFILE_ROLE' ,
'SYS_AUTH_RESOURCE_ROLE' , 'SYS_AUTH_SA_ROLE' ,
'SYS_AUTH_SPACE_ADMIN_ROLE' , 'SYS_AUTH_SSO_ROLE' ,
'SYS_AUTH_USER_ADMIN_ROLE' , 'SYS_AUTH_VALIDATE_ROLE' ,
'SYS_AUTH_WRITECLIENTFILE_ROLE' , 'SYS_AUTH_WRITEFILE_ROLE' ,
'SYS_BACKUP_DATABASE_ROLE' , 'SYS_CHANGE_PASSWORD_ROLE' ,
'SYS_CHECKPOINT_ROLE' , 'SYS_COMMENT_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_PROCEDURE_ROLE' , 'SYS_CREATE_ANY_SEQUENCE_ROLE' ,
'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' ,
'SYS_CREATE_ANY_VIEW_ROLE' , 'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_VIEW_ROLE' , 'SYS_DEBUG_ANY_PROCEDURE_ROLE' ,
'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' , 'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' ,
'SYS_DROP_ANY_PROCEDURE_ROLE' , 'SYS_DROP_ANY_SEQUENCE_ROLE' ,
'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' , 'SYS_DROP_ANY_VIEW_ROLE' ,
'SYS_DROP_CONNECTION_ROLE' , 'SYS_DROP_DATATYPE_ROLE' ,
'SYS_DROP_MESSAGE_ROLE' , 'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' ,
'SYS_MANAGE_PROFILING_ROLE' , 'SYS_MANAGE_REPLICATION_ROLE' ,
'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' ,
'SYS_REORGANIZE_ANY_OBJECT_ROLE' , 'SYS_REPLICATION_ADMIN_ROLE' ,
'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' , 'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' , 'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
ORDER BY
FK_SCHEMA,
FK_TABLE,
FK_NAME;
Function¶
Ensure your query has columns labelled as CATALOG, SCHEMA, FUNCTION_NAME, REMARKS in the SELECT list.
SELECT
null AS CATALOG,
TRIM(U.user_name) AS SCHEMA ,
TRIM(sp.proc_name) AS FUNCTION_NAME,
'' AS REMARKS
FROM
SYSPROCEDURE sp INNER
JOIN SYSPROCPARM pp
ON sp.proc_id = pp.proc_id
INNER JOIN SYSUSERPERMS U
ON U.USER_ID = SP.creator
WHERE U.USER_NAME NOT IN ('''')
AND U.USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' , 'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' ,
'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' , 'SYS_ALTER_ANY_SEQUENCE_ROLE' ,
'SYS_ALTER_ANY_TABLE_ROLE' , 'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' ,
'SYS_AUTH_OPERATOR_ROLE' , 'SYS_AUTH_PERMS_ADMIN_ROLE' ,
'SYS_AUTH_PROFILE_ROLE' , 'SYS_AUTH_READCLIENTFILE_ROLE' ,
'SYS_AUTH_READFILE_ROLE' , 'SYS_AUTH_RESOURCE_ROLE' ,
'SYS_AUTH_SA_ROLE' , 'SYS_AUTH_SPACE_ADMIN_ROLE' ,
'SYS_AUTH_SSO_ROLE' , 'SYS_AUTH_USER_ADMIN_ROLE' ,
'SYS_AUTH_VALIDATE_ROLE' , 'SYS_AUTH_WRITECLIENTFILE_ROLE' ,
'SYS_AUTH_WRITEFILE_ROLE' , 'SYS_BACKUP_DATABASE_ROLE' ,
'SYS_CHANGE_PASSWORD_ROLE' , 'SYS_CHECKPOINT_ROLE' ,
'SYS_COMMENT_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' ,
'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' ,
'SYS_CREATE_ANY_VIEW_ROLE' , 'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_VIEW_ROLE' , 'SYS_DEBUG_ANY_PROCEDURE_ROLE' ,
'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' ,
'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' ,
'SYS_DROP_ANY_PROCEDURE_ROLE' , 'SYS_DROP_ANY_SEQUENCE_ROLE' ,
'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_DROP_ANY_VIEW_ROLE' , 'SYS_DROP_CONNECTION_ROLE' ,
'SYS_DROP_DATATYPE_ROLE' , 'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' ,
'SYS_MANAGE_PROFILING_ROLE' , 'SYS_MANAGE_REPLICATION_ROLE' ,
'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' ,'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' , 'SYS_SET_USER_ROLE' ,
'SYS_SPATIAL_ADMIN_ROLE' , 'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' ,
'SYS_UPGRADE_ROLE_ROLE' , 'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' ,
'SYS_WRITE_CLIENT_FILE_ROLE' , 'SYS_WRITE_FILE_ROLE' ,
'diagnostics' , 'rs_systabgroup')
ORDER BY
SCHEMA,
FUNCTION_NAME;
Function Definition¶
Ensure your query has columns labelled as CATALOG, SCHEMA, FUNCTION_NAME, COLUMN_NAME, ARG_NAME, ARG_TYPE, TYPE_NAME, ARG_DEF, COLUMN_TYPE in the SELECT list.
SELECT
null AS CATALOG,
TRIM(U.user_name) AS SCHEMA ,
sp.proc_name AS FUNCTION_NAME,
sp.proc_defn AS ARG_DEF,
pp.parm_name AS COLUMN_NAME,
pp.base_type_str AS TYPE_NAME,
pp.base_type_str AS ARG_TYPE,
pp.parm_name AS ARG_name,
pp.parm_type AS COLUMN_TYPE
FROM
SYSPROCEDURE sp
INNER JOIN SYSPROCPARM pp
ON sp.proc_id = pp.proc_id
INNER JOIN SYSUSERPERMS U
ON U.USER_ID = SP.creator
WHERE U.USER_NAME NOT IN ('''')
AND U.USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' ,
'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' , 'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' ,
'SYS_ALTER_ANY_SEQUENCE_ROLE' , 'SYS_ALTER_ANY_TABLE_ROLE' ,
'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' ,
'SYS_ALTER_DATATYPE_ROLE' , 'SYS_AUTH_BACKUP_ROLE' ,
'SYS_AUTH_DBA_ROLE' , 'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' ,
'SYS_AUTH_OPERATOR_ROLE' , 'SYS_AUTH_PERMS_ADMIN_ROLE' ,
'SYS_AUTH_PROFILE_ROLE' , 'SYS_AUTH_READCLIENTFILE_ROLE' ,
'SYS_AUTH_READFILE_ROLE' , 'SYS_AUTH_RESOURCE_ROLE' ,
'SYS_AUTH_SA_ROLE' , 'SYS_AUTH_SPACE_ADMIN_ROLE' ,
'SYS_AUTH_SSO_ROLE' , 'SYS_AUTH_USER_ADMIN_ROLE' ,
'SYS_AUTH_VALIDATE_ROLE' , 'SYS_AUTH_WRITECLIENTFILE_ROLE' ,
'SYS_AUTH_WRITEFILE_ROLE' , 'SYS_BACKUP_DATABASE_ROLE' ,
'SYS_CHANGE_PASSWORD_ROLE' , 'SYS_CHECKPOINT_ROLE' ,
'SYS_COMMENT_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' ,
'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' ,
'SYS_CREATE_ANY_VIEW_ROLE' , 'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_MESSAGE_ROLE' , 'SYS_CREATE_PROCEDURE_ROLE' ,
'SYS_CREATE_PROXY_TABLE_ROLE' , 'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_VIEW_ROLE' , 'SYS_DEBUG_ANY_PROCEDURE_ROLE' ,
'SYS_DELETE_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_INDEX_ROLE' ,
'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' ,
'SYS_DROP_ANY_PROCEDURE_ROLE' ,
'SYS_DROP_ANY_SEQUENCE_ROLE' , 'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_DROP_ANY_VIEW_ROLE' , 'SYS_DROP_CONNECTION_ROLE' ,
'SYS_DROP_DATATYPE_ROLE' , 'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' ,
'SYS_INSERT_ANY_TABLE_ROLE' , 'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' ,
'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' ,
'SYS_MANAGE_ANY_USER_ROLE' , 'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' ,
'SYS_MANAGE_PROFILING_ROLE' , 'SYS_MANAGE_REPLICATION_ROLE' ,
'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' ,
'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' , 'SYS_SEND_EMAIL_ROLE' ,
'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' ,
'SYS_SET_USER_ROLE' , 'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' ,
'SYS_UPDATE_ANY_TABLE_ROLE' , 'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' ,
'SYS_VALIDATE_ANY_OBJECT_ROLE' ,
'SYS_WRITE_CLIENT_FILE_ROLE' , 'SYS_WRITE_FILE_ROLE' ,
'diagnostics' , 'rs_systabgroup')
ORDER BY
SCHEMA,
FUNCTION_NAME;
Index¶
Ensure your query has columns labelled as CATALOG, SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME, TYPE in the SELECT list.
SELECT
null AS CATALOG,
TRIM(I1.ICREATOR) AS SCHEMA,
TRIM(I1.TNAME) AS TABLE_NAME,
TRIM(I1.INAME) AS INDEX_NAME,
TRIM(I1.INDEXTYPE) AS TYPE,
TRIM(REPLACE(TRIM(I1.colnames),'ASC','')) AS COLUMN_NAME
FROM
SYS.SYSINDEXES I1
INNER JOIN SYS.SYSINDEX I2
ON I1.INAME = I2.INDEX_NAME
INNER JOIN SYSUSERPERMS U
ON I1.ICREATOR = U.USER_NAME
INNER JOIN SYS.SYSTABLE T
ON U.USER_ID = T.CREATOR
AND I1.TNAME = T.TABLE_NAME
WHERE U.USER_NAME NOT IN ('''')
AND U.USER_NAME NOT IN ( 'EXTENV_MAIN' , 'EXTENV_WORKER' , 'PUBLIC' ,
'SA_DEBUG' , 'SYS' , 'SYS_ACCESS_SERVER_LS_ROLE' ,
'SYS_ALTER_ANY_INDEX_ROLE' , 'SYS_ALTER_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_ALTER_ANY_OBJECT_OWNER_ROLE' , 'SYS_ALTER_ANY_OBJECT_ROLE' ,
'SYS_ALTER_ANY_PROCEDURE_ROLE' , 'SYS_ALTER_ANY_SEQUENCE_ROLE' ,
'SYS_ALTER_ANY_TABLE_ROLE' , 'SYS_ALTER_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_ALTER_ANY_TRIGGER_ROLE' , 'SYS_ALTER_ANY_VIEW_ROLE' ,
'SYS_ALTER_DATABASE_ROLE' , 'SYS_ALTER_DATATYPE_ROLE' ,
'SYS_AUTH_BACKUP_ROLE' , 'SYS_AUTH_DBA_ROLE' ,
'SYS_AUTH_MULTIPLEX_ADMIN_ROLE' , 'SYS_AUTH_OPERATOR_ROLE' ,
'SYS_AUTH_PERMS_ADMIN_ROLE' , 'SYS_AUTH_PROFILE_ROLE' ,
'SYS_AUTH_READCLIENTFILE_ROLE' , 'SYS_AUTH_READFILE_ROLE' ,
'SYS_AUTH_RESOURCE_ROLE' , 'SYS_AUTH_SA_ROLE' ,
'SYS_AUTH_SPACE_ADMIN_ROLE' , 'SYS_AUTH_SSO_ROLE' ,
'SYS_AUTH_USER_ADMIN_ROLE' , 'SYS_AUTH_VALIDATE_ROLE' ,
'SYS_AUTH_WRITECLIENTFILE_ROLE' , 'SYS_AUTH_WRITEFILE_ROLE' ,
'SYS_BACKUP_DATABASE_ROLE' , 'SYS_CHANGE_PASSWORD_ROLE' ,
'SYS_CHECKPOINT_ROLE' , 'SYS_COMMENT_ANY_OBJECT_ROLE' ,
'SYS_CREATE_ANY_INDEX_ROLE' ,
'SYS_CREATE_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_CREATE_ANY_OBJECT_ROLE' , 'SYS_CREATE_ANY_PROCEDURE_ROLE' ,
'SYS_CREATE_ANY_SEQUENCE_ROLE' , 'SYS_CREATE_ANY_TABLE_ROLE' ,
'SYS_CREATE_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_CREATE_ANY_TRIGGER_ROLE' , 'SYS_CREATE_ANY_VIEW_ROLE' ,
'SYS_CREATE_DATATYPE_ROLE' ,
'SYS_CREATE_EXTERNAL_REFERENCE_ROLE' ,
'SYS_CREATE_MATERIALIZED_VIEW_ROLE' , 'SYS_CREATE_MESSAGE_ROLE' ,
'SYS_CREATE_PROCEDURE_ROLE' , 'SYS_CREATE_PROXY_TABLE_ROLE' ,
'SYS_CREATE_TABLE_ROLE' ,
'SYS_CREATE_TEXT_CONFIGURATION_ROLE' , 'SYS_CREATE_VIEW_ROLE' ,
'SYS_DEBUG_ANY_PROCEDURE_ROLE' ,
'SYS_DELETE_ANY_TABLE_ROLE' , 'SYS_DROP_ANY_INDEX_ROLE' ,
'SYS_DROP_ANY_MATERIALIZED_VIEW_ROLE' ,
'SYS_DROP_ANY_OBJECT_ROLE' , 'SYS_DROP_ANY_PROCEDURE_ROLE' ,
'SYS_DROP_ANY_SEQUENCE_ROLE' , 'SYS_DROP_ANY_TABLE_ROLE' ,
'SYS_DROP_ANY_TEXT_CONFIGURATION_ROLE' ,
'SYS_DROP_ANY_VIEW_ROLE' ,
'SYS_DROP_CONNECTION_ROLE' , 'SYS_DROP_DATATYPE_ROLE' ,
'SYS_DROP_MESSAGE_ROLE' ,
'SYS_EXECUTE_ANY_PROCEDURE_ROLE' , 'SYS_INSERT_ANY_TABLE_ROLE' ,
'SYS_LOAD_ANY_TABLE_ROLE' ,
'SYS_MANAGE_ANY_DBSPACE_ROLE' , 'SYS_MANAGE_ANY_EVENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_ENVIRONMENT_ROLE' ,
'SYS_MANAGE_ANY_EXTERNAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_LDAP_SERVER_ROLE' ,
'SYS_MANAGE_ANY_LOGIN_POLICY_ROLE' ,
'SYS_MANAGE_ANY_MIRROR_SERVER_ROLE' ,
'SYS_MANAGE_ANY_OBJECT_PRIVILEGE_ROLE' ,
'SYS_MANAGE_ANY_SPATIAL_OBJECT_ROLE' ,
'SYS_MANAGE_ANY_STATISTICS_ROLE' , 'SYS_MANAGE_ANY_USER_ROLE' ,
'SYS_MANAGE_ANY_WEB_SERVICE_ROLE' ,
'SYS_MANAGE_AUDITING_ROLE' , 'SYS_MANAGE_CERTIFICATES_ROLE' ,
'SYS_MANAGE_MULTIPLEX_ROLE' ,
'SYS_MANAGE_PROFILING_ROLE' , 'SYS_MANAGE_REPLICATION_ROLE' ,
'SYS_MANAGE_ROLES_ROLE' ,
'SYS_MONITOR_ROLE' , 'SYS_READ_CLIENT_FILE_ROLE' ,
'SYS_READ_FILE_ROLE' , 'SYS_REORGANIZE_ANY_OBJECT_ROLE' ,
'SYS_REPLICATION_ADMIN_ROLE' , 'SYS_RUN_REPLICATION_ROLE' ,
'SYS_SELECT_ANY_TABLE_ROLE' ,
'SYS_SEND_EMAIL_ROLE' , 'SYS_SERVER_OPERATOR_ROLE' ,
'SYS_SET_ANY_PUBLIC_OPTION_ROLE' ,
'SYS_SET_ANY_SECURITY_OPTION_ROLE' ,
'SYS_SET_ANY_SYSTEM_OPTION_ROLE' ,
'SYS_SET_ANY_USER_DEFINED_OPTION_ROLE' , 'SYS_SET_USER_ROLE' ,
'SYS_SPATIAL_ADMIN_ROLE' ,
'SYS_TRUNCATE_ANY_TABLE_ROLE' , 'SYS_UPDATE_ANY_TABLE_ROLE' ,
'SYS_UPGRADE_ROLE_ROLE' ,
'SYS_USE_ANY_SEQUENCE_ROLE' , 'SYS_VALIDATE_ANY_OBJECT_ROLE' ,
'SYS_WRITE_CLIENT_FILE_ROLE' ,
'SYS_WRITE_FILE_ROLE' , 'diagnostics' , 'rs_systabgroup')
AND T.TABLE_TYPE IN ('BASE','VIEW')
AND I2.INDEX_OWNER = 'USER'
ORDER BY
INDEX_TYPE,
INDEX_NAME;