Menamai Tabel Sql Massal

Pada suatu saat, kita ingin menamai tabel MySQl beberapa buah, dan kita memerlukan kecepatan lebih daripada menamainya satu per satu, katakanlah kita sedang menggunakan alat bantu berupa HeidiSQL. Untuk mempercepat, saya melakukan hal berikut.

1. Saya peroleh dulu nama semua tabel dengan mengirim query "SHOW TABLES". Misalkan ternyata ada tabel berikut ini:

==

"pb_account"
"pb_account_group"
"pb_applied"
"pb_asset"
"pb_asset_placement"
"pb_asset_value"
"pb_bank_account"
"pb_bill"
"pb_bill_detail"
"pb_branch"
"pb_cash_transfer"
"pb_cash_transfer_detail"
"pb_cashier"
"pb_category"
"pb_client"
"pb_client_contract"
"pb_client_customer"
"pb_client_employee"
"pb_condition"
"pb_country"
"pb_currency"
"pb_customer"
"pb_customer_attribute"
"pb_customer_category"
"pb_customer_detail"
"pb_daily"
"pb_daily_category"
"pb_daily_custom"
"pb_daily_detail"
"pb_daily_employee"
"pb_daily_form"
"pb_daily_form_detail"
"pb_daily_form_detail_set"
"pb_daily_form_return"
"pb_daily_form_return_set"
"pb_daily_type"
"pb_delivery"
"pb_delivery_detail"
"pb_department"
"pb_depreciation"
"pb_driver"
"pb_employee"
"pb_employee_attribute"
"pb_employee_detail"
"pb_employee_group"
"pb_employee_schedule"
"pb_employee_schedule_detail"
"pb_event"
"pb_event_category"
"pb_good"
"pb_good_bonus"
"pb_good_capacity"
"pb_good_delivery_std"
"pb_good_detail"
"pb_good_discount"
"pb_good_group"
"pb_good_plan_std"
"pb_good_price"
"pb_good_sale_outlet_std"
"pb_good_sale_std"
"pb_good_stock_std"
"pb_good_transfer"
"pb_good_unit"
"pb_icon"
"pb_image"
"pb_joint_system"
"pb_language"
"pb_ledger"
"pb_ledger_detail"
"pb_link"
"pb_link_group"
"pb_menu"
"pb_menu_detail"
"pb_menu_distribution"
"pb_menu_group"
"pb_module"
"pb_module_category"
"pb_module_distribution"
"pb_module_link"
"pb_money"
"pb_money_detail"
"pb_money_std"
"pb_operator"
"pb_operator_client"
"pb_operator_contract"
"pb_operator_log"
"pb_operator_meta"
"pb_operator_outlet"
"pb_operator_relation"
"pb_operator_type"
"pb_outlet"
"pb_outlet_price"
"pb_payment_type"
"pb_period"
"pb_plan"
"pb_plan_detail"
"pb_post"
"pb_post_attribute"
"pb_post_category"
"pb_post_comment"
"pb_post_distribution"
"pb_post_image"
"pb_post_order"
"pb_post_page"
"pb_post_rate"
"pb_post_setting"
"pb_post_type"
"pb_post_video"
"pb_presence"
"pb_price_group"
"pb_pricing"
"pb_publicy"
"pb_return"
"pb_return_delivery"
"pb_return_detail"
"pb_role"
"pb_salary_item"
"pb_sale"
"pb_sale_detail"
"pb_sale_money"
"pb_sale_outlet"
"pb_sale_outlet_detail"
"pb_sale_outlet_money"
"pb_sale_outlet_salesman"
"pb_sale_outlet_std"
"pb_sale_salesman"
"pb_salesman"
"pb_salesman_outlet"
"pb_schedule"
"pb_schedule_detail"
"pb_schedule_group"
"pb_screen"
"pb_setting"
"pb_setting_custom"
"pb_stock"
"pb_stock_detail"
"pb_stock_employee"
"pb_stock_type"
"pb_supplier"
"pb_supplier_group"
"pb_tasting"
"pb_theme"
"pb_theme_detail"
"pb_thumbnail"
"pb_transaction"
"pb_transaction_attribute"
"pb_transaction_group"
"pb_transaction_register"
"pb_transaction_register_detail"
"pb_transaction_type"
"pb_transaction_variant"
"pb_transaction_variant_config"
"pb_translation"
"pb_tube"
"pb_tube_detail"
"pb_tube_outlet_std"
"pb_usage"
"pb_user_agent"
"pb_variant"
"pb_vehicle"
"pb_vehicle_run"
"pb_vehicle_type"
"pb_video"
"pb_voucher"
"pb_voucher_detail"
"pb_voucher_type"

==

2. Mempaste di cell Microsoft Office Excel di cell A1:

Saya ingin mengubah nama semua tabel menghilangkan awalan pb_ dari daftar tabel tersebut. Untuk mengubah satu tabel pb_account menjadi bernama account, diperlukan query: "RENAME TABLE `pb_account` TO `account`;". Ini akan digunakan sebagai dasar query, dibantu excel akan membuat query massal yang dieksekusi sekali jalan langsung mengubah nama semua tabel.

Di cell B1, dapat dibuat formula ="RENAME TABLE `"&A2&"` TO `"&MID(A2;4;LEN(A2))&"`;"

Ini adalah mengubah tabel pb_account menjadi account. Setelah formula dipaste di cell b2 hingga baris terakhir tabel, akan diperoleh daftar query. Semua query ini diselect kemudian di copy ke clipboard, nantinya dipaste di input query di HeidiSQL untuk dieksekusi sekali-jadi.

Berikut adalah query massal yang diperoleh:

==

RENAME TABLE `pb_account_group` TO `account_group`;
RENAME TABLE `pb_applied` TO `applied`;
RENAME TABLE `pb_asset` TO `asset`;
RENAME TABLE `pb_asset_placement` TO `asset_placement`;
RENAME TABLE `pb_asset_value` TO `asset_value`;
RENAME TABLE `pb_bank_account` TO `bank_account`;
RENAME TABLE `pb_bill` TO `bill`;
RENAME TABLE `pb_bill_detail` TO `bill_detail`;
RENAME TABLE `pb_branch` TO `branch`;
RENAME TABLE `pb_cash_transfer` TO `cash_transfer`;
RENAME TABLE `pb_cash_transfer_detail` TO `cash_transfer_detail`;
RENAME TABLE `pb_cashier` TO `cashier`;
RENAME TABLE `pb_category` TO `category`;
RENAME TABLE `pb_client` TO `client`;
RENAME TABLE `pb_client_contract` TO `client_contract`;
RENAME TABLE `pb_client_customer` TO `client_customer`;
RENAME TABLE `pb_client_employee` TO `client_employee`;
RENAME TABLE `pb_condition` TO `condition`;
RENAME TABLE `pb_country` TO `country`;
RENAME TABLE `pb_currency` TO `currency`;
RENAME TABLE `pb_customer` TO `customer`;
RENAME TABLE `pb_customer_attribute` TO `customer_attribute`;
RENAME TABLE `pb_customer_category` TO `customer_category`;
RENAME TABLE `pb_customer_detail` TO `customer_detail`;
RENAME TABLE `pb_daily` TO `daily`;
RENAME TABLE `pb_daily_category` TO `daily_category`;
RENAME TABLE `pb_daily_custom` TO `daily_custom`;
RENAME TABLE `pb_daily_detail` TO `daily_detail`;
RENAME TABLE `pb_daily_employee` TO `daily_employee`;
RENAME TABLE `pb_daily_form` TO `daily_form`;
RENAME TABLE `pb_daily_form_detail` TO `daily_form_detail`;
RENAME TABLE `pb_daily_form_detail_set` TO `daily_form_detail_set`;
RENAME TABLE `pb_daily_form_return` TO `daily_form_return`;
RENAME TABLE `pb_daily_form_return_set` TO `daily_form_return_set`;
RENAME TABLE `pb_daily_type` TO `daily_type`;
RENAME TABLE `pb_delivery` TO `delivery`;
RENAME TABLE `pb_delivery_detail` TO `delivery_detail`;
RENAME TABLE `pb_department` TO `department`;
RENAME TABLE `pb_depreciation` TO `depreciation`;
RENAME TABLE `pb_driver` TO `driver`;
RENAME TABLE `pb_employee` TO `employee`;
RENAME TABLE `pb_employee_attribute` TO `employee_attribute`;
RENAME TABLE `pb_employee_detail` TO `employee_detail`;
RENAME TABLE `pb_employee_group` TO `employee_group`;
RENAME TABLE `pb_employee_schedule` TO `employee_schedule`;
RENAME TABLE `pb_employee_schedule_detail` TO `employee_schedule_detail`;
RENAME TABLE `pb_event` TO `event`;
RENAME TABLE `pb_event_category` TO `event_category`;
RENAME TABLE `pb_good` TO `good`;
RENAME TABLE `pb_good_bonus` TO `good_bonus`;
RENAME TABLE `pb_good_capacity` TO `good_capacity`;
RENAME TABLE `pb_good_delivery_std` TO `good_delivery_std`;
RENAME TABLE `pb_good_detail` TO `good_detail`;
RENAME TABLE `pb_good_discount` TO `good_discount`;
RENAME TABLE `pb_good_group` TO `good_group`;
RENAME TABLE `pb_good_plan_std` TO `good_plan_std`;
RENAME TABLE `pb_good_price` TO `good_price`;
RENAME TABLE `pb_good_sale_outlet_std` TO `good_sale_outlet_std`;
RENAME TABLE `pb_good_sale_std` TO `good_sale_std`;
RENAME TABLE `pb_good_stock_std` TO `good_stock_std`;
RENAME TABLE `pb_good_transfer` TO `good_transfer`;
RENAME TABLE `pb_good_unit` TO `good_unit`;
RENAME TABLE `pb_icon` TO `icon`;
RENAME TABLE `pb_image` TO `image`;
RENAME TABLE `pb_joint_system` TO `joint_system`;
RENAME TABLE `pb_language` TO `language`;
RENAME TABLE `pb_ledger` TO `ledger`;
RENAME TABLE `pb_ledger_detail` TO `ledger_detail`;
RENAME TABLE `pb_link` TO `link`;
RENAME TABLE `pb_link_group` TO `link_group`;
RENAME TABLE `pb_menu` TO `menu`;
RENAME TABLE `pb_menu_detail` TO `menu_detail`;
RENAME TABLE `pb_menu_distribution` TO `menu_distribution`;
RENAME TABLE `pb_menu_group` TO `menu_group`;
RENAME TABLE `pb_module` TO `module`;
RENAME TABLE `pb_module_category` TO `module_category`;
RENAME TABLE `pb_module_distribution` TO `module_distribution`;
RENAME TABLE `pb_module_link` TO `module_link`;
RENAME TABLE `pb_money` TO `money`;
RENAME TABLE `pb_money_detail` TO `money_detail`;
RENAME TABLE `pb_money_std` TO `money_std`;
RENAME TABLE `pb_operator` TO `operator`;
RENAME TABLE `pb_operator_client` TO `operator_client`;
RENAME TABLE `pb_operator_contract` TO `operator_contract`;
RENAME TABLE `pb_operator_log` TO `operator_log`;
RENAME TABLE `pb_operator_meta` TO `operator_meta`;
RENAME TABLE `pb_operator_outlet` TO `operator_outlet`;
RENAME TABLE `pb_operator_relation` TO `operator_relation`;
RENAME TABLE `pb_operator_type` TO `operator_type`;
RENAME TABLE `pb_outlet` TO `outlet`;
RENAME TABLE `pb_outlet_price` TO `outlet_price`;
RENAME TABLE `pb_payment_type` TO `payment_type`;
RENAME TABLE `pb_period` TO `period`;
RENAME TABLE `pb_plan` TO `plan`;
RENAME TABLE `pb_plan_detail` TO `plan_detail`;
RENAME TABLE `pb_post` TO `post`;
RENAME TABLE `pb_post_attribute` TO `post_attribute`;
RENAME TABLE `pb_post_category` TO `post_category`;
RENAME TABLE `pb_post_comment` TO `post_comment`;
RENAME TABLE `pb_post_distribution` TO `post_distribution`;
RENAME TABLE `pb_post_image` TO `post_image`;
RENAME TABLE `pb_post_order` TO `post_order`;
RENAME TABLE `pb_post_page` TO `post_page`;
RENAME TABLE `pb_post_rate` TO `post_rate`;
RENAME TABLE `pb_post_setting` TO `post_setting`;
RENAME TABLE `pb_post_type` TO `post_type`;
RENAME TABLE `pb_post_video` TO `post_video`;
RENAME TABLE `pb_presence` TO `presence`;
RENAME TABLE `pb_price_group` TO `price_group`;
RENAME TABLE `pb_pricing` TO `pricing`;
RENAME TABLE `pb_publicy` TO `publicy`;
RENAME TABLE `pb_return` TO `return`;
RENAME TABLE `pb_return_delivery` TO `return_delivery`;
RENAME TABLE `pb_return_detail` TO `return_detail`;
RENAME TABLE `pb_role` TO `role`;
RENAME TABLE `pb_salary_item` TO `salary_item`;
RENAME TABLE `pb_sale` TO `sale`;
RENAME TABLE `pb_sale_detail` TO `sale_detail`;
RENAME TABLE `pb_sale_money` TO `sale_money`;
RENAME TABLE `pb_sale_outlet` TO `sale_outlet`;
RENAME TABLE `pb_sale_outlet_detail` TO `sale_outlet_detail`;
RENAME TABLE `pb_sale_outlet_money` TO `sale_outlet_money`;
RENAME TABLE `pb_sale_outlet_salesman` TO `sale_outlet_salesman`;
RENAME TABLE `pb_sale_outlet_std` TO `sale_outlet_std`;
RENAME TABLE `pb_sale_salesman` TO `sale_salesman`;
RENAME TABLE `pb_salesman` TO `salesman`;
RENAME TABLE `pb_salesman_outlet` TO `salesman_outlet`;
RENAME TABLE `pb_schedule` TO `schedule`;
RENAME TABLE `pb_schedule_detail` TO `schedule_detail`;
RENAME TABLE `pb_schedule_group` TO `schedule_group`;
RENAME TABLE `pb_screen` TO `screen`;
RENAME TABLE `pb_setting` TO `setting`;
RENAME TABLE `pb_setting_custom` TO `setting_custom`;
RENAME TABLE `pb_stock` TO `stock`;
RENAME TABLE `pb_stock_detail` TO `stock_detail`;
RENAME TABLE `pb_stock_employee` TO `stock_employee`;
RENAME TABLE `pb_stock_type` TO `stock_type`;
RENAME TABLE `pb_supplier` TO `supplier`;
RENAME TABLE `pb_supplier_group` TO `supplier_group`;
RENAME TABLE `pb_tasting` TO `tasting`;
RENAME TABLE `pb_theme` TO `theme`;
RENAME TABLE `pb_theme_detail` TO `theme_detail`;
RENAME TABLE `pb_thumbnail` TO `thumbnail`;
RENAME TABLE `pb_transaction` TO `transaction`;
RENAME TABLE `pb_transaction_attribute` TO `transaction_attribute`;
RENAME TABLE `pb_transaction_group` TO `transaction_group`;
RENAME TABLE `pb_transaction_register` TO `transaction_register`;
RENAME TABLE `pb_transaction_register_detail` TO `transaction_register_detail`;
RENAME TABLE `pb_transaction_type` TO `transaction_type`;
RENAME TABLE `pb_transaction_variant` TO `transaction_variant`;
RENAME TABLE `pb_transaction_variant_config` TO `transaction_variant_config`;
RENAME TABLE `pb_translation` TO `translation`;
RENAME TABLE `pb_tube` TO `tube`;
RENAME TABLE `pb_tube_detail` TO `tube_detail`;
RENAME TABLE `pb_tube_outlet_std` TO `tube_outlet_std`;
RENAME TABLE `pb_usage` TO `usage`;
RENAME TABLE `pb_user_agent` TO `user_agent`;
RENAME TABLE `pb_variant` TO `variant`;
RENAME TABLE `pb_vehicle` TO `vehicle`;
RENAME TABLE `pb_vehicle_run` TO `vehicle_run`;
RENAME TABLE `pb_vehicle_type` TO `vehicle_type`;
RENAME TABLE `pb_video` TO `video`;
RENAME TABLE `pb_voucher` TO `voucher`;
RENAME TABLE `pb_voucher_detail` TO `voucher_detail`;
RENAME TABLE `pb_voucher_type` TO `voucher_type`;

==

Jika ini dieskusi di input query HeidiSQL, selesailan pekerjaan menamai semua tabel secara massal.

SB