Magento2 : How to show custom tables fields to system configuration drop down

Question

I am developing custom module, I want to display tables fields to system configuration drop down.

How to get all tables fields and set it to in drop down for system configuration.

Thanks

Answer 1

Just create system.xml file,

app/code/Packagename/SystemConfig/etc/adminhtml/system.xml,

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Config:etc/system_file.xsd">
    <system>
        <tab id="nis" translate="label" sortOrder="200">
            <label>NIS</label>
        </tab>
        <section id="nis" translate="label" type="text" sortOrder="40" showInDefault="1" showInWebsite="1" showInStore="1">
            <label>Configuration</label>
            <tab>nis</tab>
            <resource>Packagename_SystemConfig::config_nis</resource>
            <group id="fields_list" translate="label" type="text" sortOrder="90" showInDefault="1" showInWebsite="1" showInStore="1">
                <field id="table_field" translate="label comment" type="select" sortOrder="6" showInDefault="1" showInWebsite="1" showInStore="1" canRestore="1">
                    <label>Table field</label>
                    <source_model>Packagename\SystemConfig\Model\Config\Source\Tablefield</source_model>
                </field>
            </group>
        </section>
    </system>
</config>

app/code/Packagename/SystemConfig/Model/Config/Source/Tablefield.php

<?php
namespace Packagename\SystemConfig\Model\Config\Source;

class Tablefield implements \Magento\Framework\Option\ArrayInterface
{
     public function __construct(
        \Magento\Framework\App\ResourceConnection $resource
    ) {
        $this->_resource = $resource;
    }
    /**
     * Retrieve option values array
     *
     * @return array
     */
    public function toOptionArray()
    {
        $options = [];
        $options[] = ['label' => __('Table Field'), 'value' => 'tablefield'];
        foreach ($this->getTablefield() as $field) {
            $options[] = ['label' => $field['COLUMN_NAME'], 'value' => $field['COLUMN_NAME']];
        }
        return $options;
    }
    public function getTablefield(){
        $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
        $customTable = $connection->getTableName('sales_order');
        /* you can change your magento dabase name below */
        $allField = $connection->fetchAll("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'magento2' AND TABLE_NAME = '".$customTable."'"); //magento2 is database name
        return $allField;
    }


}