You can use the UninstallInterface of your module to drop the tables during uninstall process:

app/code/Vendor/Module/Setup/Uninstall.php:

<?php

namespace Vendor\Module\Setup;

use Magento\Framework\Setup\UninstallInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class Uninstall implements UninstallInterface
{
    /**
     * Module uninstall code
     *
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @return void
     */
    public function uninstall(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $setup->startSetup();
        $connection = $setup->getConnection();
        $connection->dropTable($connection->getTableName('your_table_name_here'));
        $setup->endSetup();
    }
}

But this script works only when module has been installed using the Composer:

Hence, it is recommended that for modules NOT installed via composer, manual clean up of the database and filesystem is necessary. In module enable/disable, the code is never removed from the filesystem, so that it can be used if required at a later time. Hence, it is not removed from the setup_module table.

(additional information here).

You can use the UninstallInterface of your module to drop the tables during uninstall process:

app/code/Vendor/Module/Setup/Uninstall.php:

<?php

namespace Vendor\Module\Setup;

use Magento\Framework\Setup\UninstallInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class Uninstall implements UninstallInterface
{
    /**
     * Module uninstall code
     *
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @return void
     */
    public function uninstall(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $setup->startSetup();
        $connection = $setup->getConnection();
        $connection->dropTable($connection->getTableName('your_table_name_here'));
        $setup->endSetup();
    }
}

But this script works only when module has been installed using the Composer:

Hence, it is recommended that for modules NOT installed via composer, manual clean up of the database and filesystem is necessary. In module enable/disable, the code is never removed from the filesystem, so that it can be used if required at a later time. Hence, it is not removed from the setup_module table.

(additional information here).