I had a client who did that. Someone decided to add "Notes" to every table in the database, their call centre used the notes and found them very helpful. After a few months performance fell and they could not work out why, they paid me a lot of money to tell them it was because they were using SELECT * in their code which was now included the (huge) NOTES columns - which were unused in (most) places in the application. So SQL was retrieving it from the database, passing it to the APP and the APP was ignoring it because it wasn't needed.
If you need all 10 (say) columns now, name them. If you add a column change the application to add its name ONLY where it is actually used.
Other problem I have seen is where someone decides that the sequence of columns in the table should be changed to group columns "Logically" together in the table. Then the SELECT * presents the columns in the new order - and whatever is receiving the columns gets them in an unexpected order and raised error or does something unexpected. (Of course you can specify columns from a resultset by name, in your APP, but that is generally slower than using an ordinal position, so more often ordinal position is used.
If you retrieve a column that is not used in your APP then SQL will still retrieve it from the database. Also, SQL may not use some indexes as it cannot get All Columns from the index (whereas if you said SELECT Col1, Col2 (only) and there was an index that had both columns then SQL would use that - much faster to get that from the index than actually getting the row from the database table itself.
There are legitimate reasons for using SELECT * - for example "Export this table, all columns, whatever they are, in whatever order they might be", but that's about the only legitimate situation I can think of just now