功能:shell获取多个oracle库mysql库所有的表主键
输入:数据库连接串配置文件
输出:每张表的属主,表名,主键
shell获取mysql所有表的主键脚本如下:
#!/bin/bash
# 获取mysql所有表的主键
# get_mysql_tab_pk.sh
# mysql连接串示例 jdbc:mysql://10.23.128.146:4005/tpi|tpi_uat|uat$2022%
[ -e get_all_tab_mysql_pk.log ] && rm get_all_tab_mysql_pk.log
cat mysql_conn_str.conf |while read line
do
url=`echo $line|cut -d "|" -f 1`
user=`echo $line|cut -d "|" -f 2`
pwd=`echo $line|cut -d "|" -f 3`
my_sql="
select
CONSTRAINT_SCHEMA,table_name,group_concat(column_name order by ordinal_position separator ',') as pkcols
from information_schema.key_column_usage t
group by CONSTRAINT_SCHEMA,table_name
"
/tpdata/sqoop_tool/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop eval \
--connect "${url}" \
--username "${user}" \
--password "${pwd}" \
--driver com.mysql.jdbc.Driver \
--query "${my_sql}" >> get_all_tab_mysql_pk.log
done
shell获取oracle所有表的主键脚本如下:
#!/bin/bash
# 获取oracle所有表的主键
# get_oracle_tab_pk.sh
# oracle连接串示例 jdbc:oracle:thin:@10.38.134.45:1521/tpi|tpiquery|ledger%2021
[ -e get_all_tab_oracle_pk.log ] && rm get_all_tab_oracle_pk.log
cat oracle_conn_str.conf |while read line
do
url=`echo $line|cut -d "|" -f 1`
user=`echo $line|cut -d "|" -f 2`
pwd=`echo $line|cut -d "|" -f 3`
ora_sql="
SELECT OWNER,table_name,LISTAGG(column_name,',') WITHIN group(ORDER BY column_name) AS pkcols
FROM
(
select a.OWNER,a.table_name,a.column_name
from all_cons_columns a
join all_constraints b
on a.constraint_name = b.constraint_name
and a.OWNER=b.OWNER
and b.constraint_type = 'P'
) t1
GROUP BY OWNER,table_name
"
/tpdata/sqoop_tool/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop eval \
--connect "${url}" \
--username "${user}" \
--password "${pwd}" \
--query "${ora_sql}" >> get_all_tab_oracle_pk.log
done
