Most Drupal modules that installed their own database tables also remove them upon uninstall. I have bolded uninstall due to the fact that modules can have two states when they are turned off namely disabled and uninstalled.
Disabled implies that you have simply turned the module off, but have chosen to keep the module's settings and database tables intact in the event that you turn it back on again at some point. Disabling is done by simply unchecking the module on the module administration page and clicking on "Save configuration".
Uninstalled on the other hand indicates that you have decided that a previously disabled module will never be used again. Consequently, you are happy to remove all the settings and database tables created. This is done by clicking on the "Uninstall" tab on the module administration page, checking the module that you want to uninstall and then clicking on "Uninstall".
If for some reason the module does not have an uninstallation script in its .install file, then you will need to remove the tables manually. You can also try creating an issue in the module's issue queue asking for this oversight to be remedied.
If you have removed the module's files from the server, then you will (obviously) be unable to run the uninstallation script (as the module will no longer be listed under the uninstall tab). To fix this, simply download/restore the module again to the file system (you do not need to install it). This should list it again under the uninstall tab and you can proceed to run its uninstallation script as above.