Loop through all tables and columns

Is there a way to change the collation against all the tables and columns within a database?Easy way? No. Best way is probably to select from the information_schema views, and generate a set of alter table commands. Save that script, in case you need it again. I don't have the syntax on hand, but I think

alter table blah alter column yadda varchar(nnn) collate SQL_Latin1...

In order to get the data type correct, you can disect the sp_help stored procedure to see how MS does it.|||Is there a way to change the collation against all the tables and columns within a database?

look this example query,This query will generate 'alter table' statement.u can specify ur filter condition in where clause for ur specfic requirment.Use this code by your own risk.

SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE SQL_Latin1_General_CP850_BIN '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END

FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')