获取某数据库中含有某字段的所有表的脚本

背景

在某些情况下，比如业务重构时，需要对涉及到某字段的代码进行修改，则需要获取到某数据库中所有含有该字段的数据表．

代码

<?php$dbhost = 'ip';
$dbusername = 'username';
$dbpass = 'password';
$dbname = 'databasename';$db_connect = new mysqli($dbhost, $dbusername, $dbpass, $dbname);// 获取数据库中各个表的基础信息
$sql = 'SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA="'.$dbname.'"';$result = $db_connect->query($sql);while ($row = mysqli_fetch_assoc($result)) {$tables[] = $row;
}//取出所有表的名称
$tables = array_column($tables, 'TABLE_NAME');//要查询的字段名
$name = 'abc';
//含有该字段名的表名称数组
$needs = array();
// 对各个表做遍历，获取含有该字段的表名称
foreach ($tables as $table) {$sql = 'select '.$name.' from '.$table;if ($result = $db_connect->query($sql)) {$needs[] = $table;}
}return $needs;

嵌套查询sql语句？测试

以某数据库和某字段为样例进行测试，返回结果如下

/opt/wwwroot/test/dbcolume.php:34:
array(32) {[0] =>string(20) "account_organization"[1] =>string(21) "app_recruit_interview"[2] =>string(26) "campus_recruit_interviewer"[3] =>string(18) "campus_recruit_log"[4] =>string(35) "campus_recruit_site_address_element"[5] =>string(33) "campus_recruit_site_group_element"[6] =>string(24) "campus_recruit_site_plan"[7] =>string(32) "campus_recruit_site_time_element"[8] =>string(8) "employee"[9] =>string(34) "interviewer_remark_template_config"[10] =>string(18) "operation_integral"[11] =>string(17) "position_projects"[12] =>string(11) "preemployee"[13] =>string(7) "recruit"[14] =>string(19) "recruit_config_lock"[15] =>string(17) "recruit_interview"[16] =>string(25) "recruit_interview_history"[17] =>string(24) "recruit_interview_status"[18] =>string(12) "recruit_lock"[19] =>string(13) "recruit_offer"[20] =>string(15) "recruit_process"[21] =>string(21) "recruit_process_alias"[22] =>string(14) "recruit_rounds"[23] =>string(16) "recruitflow_step"[24] =>string(24) "recruitflow_step_history"[25] =>string(23) "resume_background_check"[26] =>string(23) "resume_candidate_upload"[27] =>string(13) "resume_hiring"[28] =>string(25) "resume_remuneration_proof"[29] =>string(10) "statistics"[30] =>string(19) "statistics_archives"[31] =>string(12) "vanke_reward"
}