One use for this sort of thing (but with conditional self-modification, raher than unconditional as in the question) is code that changes its behavious the first time it is run after certain kinds of intrusion are detected. I'm not sure if this is useful in the T-SQL case, but it has been used in other languages (where it tends to be much more diffiult than it is in T-SQL).

And as for code that drops itself - I used to use that sometimes when I want to be sure something only happens once, as part of a belt and braces approach to some kinds of data upgrade: the proc that does the upgrade firsts drops itself so that no-one can try calling it again, then checks whether the upgrade has already been done by seeing if a "this is already done" flag exists, then does the upgrade (altering much data) and creates the flag. Now I don't do it that way any more - not sure why.