达梦数据库获取主外键约束信息
程序员文章站
2022-06-03 12:24:10
...
达梦数据库获取约束信息
在达梦数据库中,我们有时候需要查询主外键信息,通过如下的SQL语句可以直接获取
--DM7获取主键信息
select
t1.table_name ,
t1.constraint_name,
t2.column_name
from
all_constraints t1,
dba_cons_columns t2
where
t1.constraint_name=t2.constraint_name
and t1.constraint_type='P'
order by
t2.position asc
--主键语句
select 'alter table '||owner||'.'||table_name||' add constraint '||constraint_name||' primary key('||wm_concat(column_name)||');'
from
(
select
t1.owner,
t1.table_name ,
t1.constraint_name,
t2.column_name
from
all_constraints t1,
dba_cons_columns t2
where
t1.constraint_name=t2.constraint_name
and t1.constraint_type='P'
order by
t2.position asc
)
group by
owner,
table_name ,
constraint_name
order by
owner,
table_name ,
constraint_name
--DM7获取外键信息
select
FK_OWNER,
FK_TABLE,
FK ,
FK_COL ,
PK_OWNER,
PK_TABLE,
B.PK_COL
FROM
(
SELECT
t1.owner AS FK_OWNER,
t1.table_name AS FK_TABLE,
t1.constraint_name AS FK ,
t3.column_name AS FK_COL ,
T2.OWNER AS PK_OWNER,
t2.table_name AS PK_TABLE,
t1.r_constraint_name AS PK ,
T1.DELETE_RULE
from
dba_constraints t1,
dba_constraints t2,
DBA_CONS_COLUMNS t3
where
t1.constraint_type='R'
and t2.constraint_type='P'
and t2.constraint_name=t1.r_constraint_name
and t3.constraint_name=t1.constraint_name
)
A
LEFT JOIN
(
SELECT
T1.constraint_name AS PK,
T2.column_name AS PK_COL
FROM
dba_constraints T1,
DBA_CONS_COLUMNS T2
WHERE
T1.CONSTRAINT_TYPE='P'
AND T1.constraint_name=T2.constraint_name
)
B
ON
A.PK=B.PK
--DM6获取主键信息
SELECT
A.NAME AS CONSTRAINT_NAME,
B.NAME AS TABLE_NAME,
E.NAME AS COLUMN_NAME,
'P' AS CONSTRAINT_TYPE
FROM
SYSDBA.SYSTABLES B,
SYSDBA.SYSCONSTRAINTS A,
SYSDBA.SYSINDEXES C,
SYSDBA.SYSINDEXKEYS D,
SYSDBA.SYSCOLUMNS E
WHERE
A.TABLEID=B.ID
AND A.TABLEID=C.TABLEID
AND C.ID =D.ID
AND D.COLID =E.COLID
AND A.TABLEID=E.ID
AND A.TYPE ='P'
AND A.NAME =C.NAME
AND B.TYPE ='U'
--DM6获取主键语句
select
'alter table '||table_name||' add constraint PK_'||replace(table_name,'.','_')||' primary key('||column_name||');'
from
( select
table_name,
ltrim(max(sys_connect_by_path(COLUMN_NAME, ',')), ',') as column_name
from
( select
row_number() over(PARTITION by table_name ORDER by COLUMN_NAME) r,
TABLE_NAME,
COLUMN_NAME
from
( SELECT
u.name||'.'||
B.NAME AS TABLE_NAME,
E.NAME AS COLUMN_NAME
FROM
SYSDBA.SYSTABLES B,
SYSDBA.SYSCONSTRAINTS A,
SYSDBA.SYSINDEXES C,
SYSDBA.SYSINDEXKEYS D,
SYSDBA.SYSCOLUMNS E,
SYSDBA.SYSSCHEMAS U
WHERE
A.TABLEID=B.ID
AND A.TABLEID=C.TABLEID
AND C.ID =D.ID
AND D.COLID =E.COLID
AND A.TABLEID=E.ID
AND A.TYPE ='P'
AND A.NAME =C.NAME
AND B.TYPE ='U'
AND U.SCHID=B.SCHID
order by TABLE_NAME,d.keyNO ))
start
with
r=1 CONNECT by prior r =r-1
and prior table_name = table_name
group by
table_name
order by
table_name)
--DM6获取外键信息
select
fk_tab.OWNER AS FK_OWNER,
fk_tab.table_name as FK_TABLE,
fk_tab.constraint_name as fk,
fk_tab.col_name as fk_col,
PK_TAB.PK_OWNER,
pk_tab.table_name as pk_table,
PK_TAB.CONSTRAINT_NAME,
pk_tab.colUMN_name as pk_col
from
(
select
P1.OWNER,
p1.constraint_name,
p1.table_name,
p1.col_name,
P2.NAME AS R_CONSTRAINT_NAME
from
(
select
e.id,
a.name as constraint_name,
c.name as table_name,
d.name as col_name,
'R' as constraint_type ,
F.NAME AS OWNER
from (select name,id,tableid from sysdba.sysindexes where exists
(
select
1
from
sysdba.sysrefconstraints
where
exists
(
select
1
from
sysdba.sysindexes
where
exists
(
select
1
from
sysdba.sysconstraints j
where
j.type='P'
and j.tableid >999
and name =sysdba.sysindexes.name
)
and id= sysdba.sysrefconstraints.rid
)
and fid= sysdba.sysindexes.id
))
a,
sysdba.sysindexkeys b,
sysdba.systables c,
sysdba.syscolumns d,
sysdba.sysconstraints e ,
SYSDBA.SYSSCHEMAS F
where
a.id = b.id
and a.tableid = c.id
and c.id = d.id
and b.colid = d.colid
and c.type ='U'
and a.name =e.name
AND C.SCHID=F.SCHID
)
p1
LEFT JOIN
(
SELECT
E.NAME,
C.ID
FROM
SYSDBA.SYSCONSTRAINTS C,
SYSDBA.SYSREFCONSTRAINTS D,
SYSDBA.SYSINDEXES E
WHERE
C.ID=D.ID
AND D.RID =E.ID
)
P2
ON
P1.ID=P2.ID
)
FK_TAB,
(
SELECT
F.NAME AS PK_OWNER,
A.NAME AS CONSTRAINT_NAME,
B.NAME AS TABLE_NAME,
E.NAME AS COLUMN_NAME,
'P' AS CONSTRAINT_TYPE
FROM
SYSDBA.SYSTABLES B,
SYSDBA.SYSCONSTRAINTS A,
SYSDBA.SYSINDEXES C,
SYSDBA.SYSINDEXKEYS D,
SYSDBA.SYSCOLUMNS E ,
SYSDBA.SYSSCHEMAS F
WHERE
A.TABLEID=B.ID
AND A.TABLEID=C.TABLEID
AND C.ID =D.ID
AND D.COLID =E.COLID
AND A.TABLEID=E.ID
AND A.TYPE ='P'
AND A.NAME =C.NAME
AND B.TYPE ='U'
AND B.SCHID=F.SCHID
)
PK_TAB
WHERE
FK_TAB.R_CONSTRAINT_NAME=PK_TAB.CONSTRAINT_NAME
上一篇: docker迁移数据目录
下一篇: 在虚拟主机上复制安装本机Pear