Here is the video where I discuss how you can easily change the database collation for the database and also for the table inside the database.
If you have a database with lots of tables, you probably will have to do this for every single table inside your database and that may be very cumbersome as well. While there are so many scripts available on the internet, it is always difficult to select one which works without any error.
I suggest that any script you choose (including mine), I strongly suggest that you should try that out on your development environment first before you deploy that on the production environment. In the past, I have seen when you change the collation of the database, and when it is very much different from the original database, it actually changes the value of the string when retrieved. While this has happened rarely, in any case, you should always do any changes to your development environment first before bringing them to life.
Reference: Pinal Dave (https://blog.sqlauthority.com)