I would like to know if someone can help me change the collation of a database. I know the command but need to change every single table and every field in ever table as well as the views, stored procedures and functions collation. It is not an option to do it manually as the are over 1,000 talbles, a hundred stored procedures and multiple views and functions.
Can someone help me with this? Possibly a script to change everything in one go?
Procs and views need to be changed if you have explicit COLLATE clauses in the code. You should be able to do a search and replace in your source control system to do that, assuming you have one (if not you should probably invest in a source control solution :-).
For tables the main problem is constraints and indexes that are dependent on collation. They will need to be droppped and recreated in the right order. If the database is small enough then the simplest fix may be to script the entire DB, search and replace the collation name in the script, re-create it and then reload the data from the old system (using SSIS or BCP for example).
The amount of testing you then need to do depends a lot on what the change is, whether you need to deal with mixed collations anwhere and whether you have inputs that may violate constraints. Changes to case-sensitivity for example will almost always require full regression testing and code changes, especially if you want to change from a case-insensitive (CI) database or server collation to a case-sensitive (CS) one.
So the short answer is that there unfortunately aren't any guaranteed shortcuts you can take. You could get started by generating a script from the information schema:
SELECT 'ALTER TABLE '
+' ALTER COLUMN '
+'('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4))+') COLLATE Latin1_General_CI_AS'
WHERE DATA_TYPE IN ('VARCHAR','CHAR','NVARCHAR','NCHAR')
AND COLLATION_NAME <> 'Latin1_General_CI_AS';