The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn't in a literal sense, nor does that approach update any user databases to match the new collation.
I posted a detailed analysis of this undocumented approach, and the implications of making any collation changes to an instance and/or database, here:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
There are quite a few areas that could be affected, and it depends greatly on if you are talking about just one particular database, or the instance and one or more of the databases on that instance, and if you have any
VARCHAR data containing characters with values of 128 - 255, and if you are switching sensitivities (e.g. case-sensitive to case-insensitive, or vice-verse).
With regards to the
ALTER DATABASE ... COLLATE ... option:
- That does not work on system databases.
- That is not permitted if you have one of many dependencies (e.g. TVFs returning string columns, computed column returning string types, etc).
- For user databases where it is permitted, it only changes the system tables (e.g.
sys.schemas, etc); it does not change any user tables (columns or indexes)