/
DatabaseMetaData_GetCrossReferences.sql
48 lines (45 loc) · 3.46 KB
/
DatabaseMetaData_GetCrossReferences.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
/*
* Copyright 2019 Google LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
SELECT *
FROM (
SELECT PARENT.TABLE_CATALOG AS PKTABLE_CAT, PARENT.TABLE_SCHEMA AS PKTABLE_SCHEM, PARENT.TABLE_NAME AS PKTABLE_NAME,
PARENT.COLUMN_NAME AS PKCOLUMN_NAME, CHILD.TABLE_CATALOG AS FKTABLE_CAT, CHILD.TABLE_SCHEMA AS FKTABLE_SCHEM,
CHILD.TABLE_NAME AS FKTABLE_NAME, CHILD.COLUMN_NAME AS FKCOLUMN_NAME, CHILD.ORDINAL_POSITION AS KEY_SEQ, 3 AS UPDATE_RULE,
CASE WHEN TABLES.ON_DELETE_ACTION='CASCADE' THEN 0 ELSE 3 END AS DELETE_RULE, NULL AS FK_NAME, 'PRIMARY_KEY' AS PK_NAME,
7 AS DEFERRABILITY
FROM INFORMATION_SCHEMA.INDEX_COLUMNS PARENT
INNER JOIN INFORMATION_SCHEMA.INDEXES PARENT_INDEX ON PARENT.INDEX_NAME=PARENT_INDEX.INDEX_NAME AND PARENT.TABLE_NAME=PARENT_INDEX.TABLE_NAME AND PARENT.TABLE_SCHEMA=PARENT_INDEX.TABLE_SCHEMA AND PARENT.TABLE_CATALOG=PARENT_INDEX.TABLE_CATALOG AND PARENT_INDEX.INDEX_TYPE='PRIMARY_KEY'
INNER JOIN INFORMATION_SCHEMA.INDEX_COLUMNS CHILD ON NOT PARENT.TABLE_NAME=CHILD.TABLE_NAME AND PARENT.COLUMN_NAME=CHILD.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.INDEXES CHILD_INDEX ON CHILD.INDEX_NAME=CHILD_INDEX.INDEX_NAME AND CHILD.TABLE_NAME=CHILD_INDEX.TABLE_NAME AND CHILD.TABLE_SCHEMA=CHILD_INDEX.TABLE_SCHEMA AND CHILD.TABLE_CATALOG=CHILD_INDEX.TABLE_CATALOG AND CHILD_INDEX.INDEX_TYPE='PRIMARY_KEY'
INNER JOIN INFORMATION_SCHEMA.TABLES ON CHILD.TABLE_CATALOG=TABLES.TABLE_CATALOG AND CHILD.TABLE_SCHEMA=TABLES.TABLE_SCHEMA AND CHILD.TABLE_NAME=TABLES.TABLE_NAME AND PARENT.TABLE_NAME=TABLES.PARENT_TABLE_NAME
WHERE PARENT_INDEX.INDEX_TYPE='PRIMARY_KEY' AND CHILD.ORDINAL_POSITION IS NOT NULL
UNION ALL
SELECT PARENT.TABLE_CATALOG AS PKTABLE_CAT, PARENT.TABLE_SCHEMA AS PKTABLE_SCHEM, PARENT.TABLE_NAME AS PKTABLE_NAME,
PARENT.COLUMN_NAME AS PKCOLUMN_NAME, CHILD.TABLE_CATALOG AS FKTABLE_CAT, CHILD.TABLE_SCHEMA AS FKTABLE_SCHEM,
CHILD.TABLE_NAME AS FKTABLE_NAME, CHILD.COLUMN_NAME AS FKCOLUMN_NAME, CHILD.ORDINAL_POSITION AS KEY_SEQ, 3 AS UPDATE_RULE,
3 AS DELETE_RULE, CONSTRAINTS.CONSTRAINT_NAME AS FK_NAME, CONSTRAINTS.UNIQUE_CONSTRAINT_NAME AS PK_NAME,
7 AS DEFERRABILITY
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CHILD ON CONSTRAINTS.CONSTRAINT_CATALOG=CHILD.CONSTRAINT_CATALOG AND CONSTRAINTS.CONSTRAINT_SCHEMA= CHILD.CONSTRAINT_SCHEMA AND CONSTRAINTS.CONSTRAINT_NAME= CHILD.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PARENT ON CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG=PARENT.CONSTRAINT_CATALOG AND CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA=PARENT.CONSTRAINT_SCHEMA AND CONSTRAINTS.UNIQUE_CONSTRAINT_NAME=PARENT.CONSTRAINT_NAME AND PARENT.ORDINAL_POSITION=CHILD.POSITION_IN_UNIQUE_CONSTRAINT
) AS CROSS_REF
WHERE UPPER(PKTABLE_CAT) LIKE ?
AND UPPER(PKTABLE_SCHEM) LIKE ?
AND UPPER(PKTABLE_NAME) LIKE ?
AND UPPER(FKTABLE_CAT) LIKE ?
AND UPPER(FKTABLE_SCHEM) LIKE ?
AND UPPER(FKTABLE_NAME) LIKE ?
ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ