What games change completely with mods?

Gothic 2 with "The Chronicles Of Myrtana: Archolos"

Mount & Blade: Warband
There are so many mods that change the game setting.

Minecraft

Minecraft mods can literally change the whole game

Mount & Blade (namely Warband) can change the world, story line and even factions.For example there is a mod that change the world into that of Game of Thrones, including the factions.

Space Engineers mods can be something simple like a new block or blockpack, to whole physics changes (like re-entry heat or adding water to the planets). Or add dangers and threats to the world so you have a purpose to create stuff other than "just cause".

Euro Truck Simulator 2.
So many mods, like the "Mapa EAA" in Brazil, with a bus mod. Or the "TruckersMP", where they did the game a MMO.

The Witcher 3 cape mod, you get a cape.

counter strike started as a mod
so did day z

Anything from bethesda.

Recently, the most interesting mod i've come across is the CK3 - Mount and Blade Bannerlord mod. I am still looking at it with intrigue. It just works! I only have Bannerlord tho.
Cheers~

Silent Hunter 3 GWX3 GOLD:
http://www.thegreywolves.com/

If you've ever played the original Doom, you should give "Brutal Doom" a try, although I would say you should play the original first if you haven't already. From Wikipedia:

Brutal Doom adds many gameplay features, such as blood splattering, allied Marines, an updated particle system, the ability to drive vehicles such as tanks, stealth kills, headshots, and a host of "Mortal Kombat-esque fatality animations." Enemy AI has been revamped, with most enemies gaining new attacks and behaviors.

Everlasting Summer))

well, back in the day....
a product called X-Men: the Ravages of Apocalypse was a total conversion of Quake.
Strangely, you don't play an X-Man. You fight multiple clones of the X-Men, if I recall correctly.

Civ games have mods to change rules/units/factions that would change the game a lot
XCOM did the same, some say "Long War" is the real game
Skyrim had a mod that turned into its own game
Tabletop sim, every mod is a brand new game
and plenty of indie games make it easy to mod (steam workshop)

Red Dead Redemption 2 has trainers (Lenny's Simple Trainer for one) which allow you to turn Arthur into animals. Which playing with imposed rules on my part where I couldn't let anyone see me turn into an animal, and I could only turn into animals I first got into close contact with, essentially let me play the best Animorphs game possible.

Crusader Kings 2's game of thrones mod is not only a total conversion to play the game in Westeros, but makes changes to the mechanics that makes the game play more like the novels than the vanilla game.

Half Life community has always been mod-happy. Counter Strike for example is a game that came out of a Half Life mod.
And Half Life: Alyx is no exception.

It was released a little over a year ago, and already has some awesome total conversion mods.
They let you play various community made scenarios, etc.
But the biggest of them, let you play an entirely different game.
Like the one that allows you to play Bioshock in VR.
Or a new one, that's in the works, that will allow you to play Portal in VR...

Enderal for skyrim - i liked it

StarCraft 2 ---> Diablo 2 remake.
There are several heavily modified maps for StarCraft 2, they even made a remake of Diablo 2 that way!
https://www.youtube.com/watch?v=phnlkg-huRs

city builders are widely better with mods (sc4, cities skylines) , but you probably ever know it
i didnt play it since a while but some websites are (or were) huge resources : SC4Devotion, Simtropolis, and more

Technically an official mod but Kaycee's Mod for Inscryption. Only act 1, randomized bosses, way harder, no death cards, etc.

Also, I haven't tried it, but the mods for Sims 4 apparently change a lot.

Daggerfall/Daggerfall Unity

View attached image.

View attached image.

Skyrim, tons of mods that change everything from the environment to adding extra lands and dungeons.

Enderal: Forgotten Stories (Special Edition) derived from Skyrim
feels like playing gothic