I want to get schema name, table name and a boolean column for primary keys (if primary key exists in the table).
I used this query to do the task:
select DISTINCT row_number() over(order by q.Schema, q.TableName, q.constraint_type) as Id
,q.Schema
,q.TableName
,q.constraint_type
from (
select at.owner as Schema
, at.table_name as TableName
,ac.constraint_type
from ALL_TABLES at
left join ALL_CONSTRAINTS ac
on ac.owner = at.owner and ac.table_name = at.table_name
where at.temporary = 'N'
and at.owner in ( 'Source_schema' )
and at.owner not in ( 'CTXSYS', 'MDSYS', 'SYSTEM', 'XDB','SYS' )
and ac.constraint_type in ('P','R','C') or ac.constraint_type != null
group by at.owner, at.table_name,ac.constraint_type ) q
order by q.TableName asc
I want to get all the table names and in the third column I want to know if that table contains a primary key.
With this query, I am getting duplicate table names because it can contain more than one keys. But I only want to have a flag which tells if there is a primary key in that table.
Here is the output of the above query which has duplicate table names:
CodePudding user response：
Isn't that what you asked for? This condition:
and ac.constraint_type in ('P','R','C')
will return 3 constraint types, while you need only primary keys. Therefore, remove R and C.
Alternatively, if you modify query a little bit so that constraint_type contains all types and add another column (a flag, which shows whether that table contains a primary key or not), you'd get this (I commented filter at line #16):
SQL> SELECT ROW_NUMBER () OVER (ORDER BY q.schema, q.tablename) AS id,
2 q.schema,
3 q.tablename,
4 LISTAGG (q.constraint_type, ', ')
5 WITHIN GROUP (ORDER BY q.constraint_type) constraint_type,
6 MAX (CASE WHEN q.constraint_type = 'P' THEN '*' ELSE NULL END) contains_pk
7 FROM (SELECT DISTINCT
8 at.owner AS schema,
9 at.table_name AS tablename,
10 ac.constraint_type
11 FROM all_tables at
12 LEFT JOIN all_constraints ac
13 ON ac.owner = at.owner
14 AND ac.table_name = at.table_name
15 WHERE at.temporary = 'N'
16 --AND at.owner IN ('Source_schema') --> commented
17 AND at.owner NOT IN ('CTXSYS',
18 'MDSYS',
19 'SYSTEM',
20 'XDB',
21 'SYS')
22 AND ac.constraint_type IN ('P', 'R', 'C')
23 OR ac.constraint_type IS NOT NULL) q
24 GROUP BY q.schema, q.tablename
25 ORDER BY q.tablename ASC;
Result:
ID SCHEMA TABLENAME CONSTRAINT C
---------- -------------------- ------------------------------ ---------- -
1 SCOTT ADVISER P, R *
2 SCOTT BOOK_NAMES C, P *
3 SCOTT CARTE P, R *
4 SCOTT COMPTE P *
5 SCOTT DEPARTMENT P *
6 SCOTT DOCUMENT_TO_DROP P *
7 SCOTT EMP C
8 SCOTT EMPLEADO1 C
9 SCOTT EMPLEADO2 C
10 SCOTT EMPLOYEES C
11 SCOTT FILIALI_CHIUSE C
12 SCOTT NEW_DEPT P *
13 SCOTT NOVCET P *
14 SCOTT ORDERS P *
15 SCOTT PERSONAL_INFO C
16 SCOTT RECORDING C, P, R *
17 SCOTT TEACHER P *
18 SCOTT VIEWS P *
18 rows selected.
SQL>
[EDIT] According to comments you posted, you'd want to see tables that don't have any constraints. Currently, you can't because of conditions you put into the query - you explicitly specified that constraint type can't be NULL (note that such a condition isn't != null but is not null). Therefore, remove it. For example:
SQL> WITH
2 data
3 AS
4 (SELECT DISTINCT at.owner, at.table_name, ac.constraint_type
5 FROM all_tables at
6 LEFT JOIN all_constraints ac
7 ON ac.owner = at.owner
8 AND ac.table_name = at.table_name
9 WHERE at.temporary = 'N'
10 AND at.owner NOT IN ('CTXSYS',
11 'MDSYS',
12 'SYSTEM',
13 'XDB',
14 'SYS'))
15 SELECT ROW_NUMBER () OVER (ORDER BY owner, table_name) rn,
16 owner,
17 table_name,
18 LISTAGG (constraint_type, ', ')
19 WITHIN GROUP (ORDER BY constraint_Type) constraint_type,
20 MAX (CASE WHEN constraint_type = 'P' THEN '*' ELSE NULL END) contains_pk
21 FROM data
22 GROUP BY owner, table_name
23 ORDER BY owner, table_name;
Result:
RN OWNER TABLE_NAME CONSTRAINT_TYPE CONTAINS_PK
---------- --------------- ------------------------------ --------------- -----------
1 ISPO TOAD_PLAN_TABLE
2 SCOTT ACCNT_PROFILE_SPCL
3 SCOTT ADVISER P, R *
4 SCOTT AUSRÜSTUNG
5 SCOTT AUTH_USER_REGISTRATION
6 SCOTT BF_USER
7 SCOTT BONUS
8 SCOTT BOOK_NAMES C, P *
9 SCOTT CARTE P, R *
10 SCOTT CATALOG
<snip>
71 SCOTT TUSER
72 SCOTT VIEWS P *
73 SCOTT WAFFEN
73 rows selected.
SQL>