Question: What are the adverse effects of having too many lookup tables in the database?

I have to incorportate too many Enumerations, based on the applications. 

What would experts advice?

Answer: 
Initially you have to ask yourself "how many is too many?". If there is a logical relation between two tables, there has to be a FK.

If you don't need the related tables anywhere within the database, you could consider to remove them and use a CHECK constraint with an "IN" clause to enforce data validity. Though, this would cause an alteration of the table with each new value within the enumeration.

My personal advice is to keep the FKs and the tables. It's a clear solution and the database is way better to maintain if there is a describing text available for all those *numbers*.
The Whole point of lookup data is that there is a finite list of valid identifiers for a specific field. if those specific fields are used in procedures or where statements to determine the correct process path or the limit the select list, then there is no such thing as too many lookups.

if it is not a finite list of identifiers for a specific process or where clause then they should not be a lookup value.

two types of fields that come to mind which might be considered lookup values but don't necessarily need to be.

City and Province/state:

There is a finite list of these but because there are sooo many you might not want to make a lookup for these.