The worst-case Y2038 scenarios are roughly the same as those predicted for Y2K. In fact, the chance of catastrophic events is much greater for Y2038 than Y2K since Y2038 is fundamental to the representation of time within computers, whereas Y2K was mostly just an issue of time interpretation by humans.
The absolute worst case scenario is one in which all infrastructure components simultaneously and completely stop working, including power utilities, transportation, satellites, communication, Internet, banking, commerce, medical devices, etc. Electric power plants could go off line. Nuclear power plants could experience core melt downs. Military munitions could misfire or become non-functional. Cars, planes, trains, boats, submarines, etc. may not start, fail abruptly, or behave erratically. Global communication could come to a halt. The Internet could entirely or partially stop working. Satellites may cease to function and possibly fall out of orbit. Many devices with embedded computers may malfunction, including cell-phones, pace makers, ATMs, and stop-lights. Non-compliant databases could easily cause a fair amount of confusion until the problems are resolved.
Fortunately, there is still plenty of time left to address all of these potential issues so that hopefully no major catastrophes occur.