declare @maco table (number int,myvalue varchar(2))

insert into @maco select 1,'aa' union all

select 2,'Aa' union all

select 3,'aA' union all

select 4,'AA'

--不区分大小写

select * from @maco WHERE nn='aa'

--区分大小写

select  * from @maco WHERE nn='aa' collate Chinese_PRC_CS_AI

--不区分大小写

ALTER TABLE My_table ALTER Column colname nvarchar(100) collate Chinese_PRC_CI_AS     

--区分大小写  

ALTER TABLE My_table ALTER Column colname nvarchar(100) collate Chinese_PRC_CS_AS

--不区分大小写  

ALTER DATABASE databasename collate Chinese_PRC_CI_AS    

--区分大小写   

ALTER DATABASE databasename collate Chinese_PRC_CS_AS

DECLARE @collate nvarchar(100);

DECLARE @table nvarchar(255);

DECLARE @column_name nvarchar(255);

DECLARE @column_id int;

DECLARE @data_type nvarchar(255);

DECLARE @max_length int;

DECLARE @row_id int;

DECLARE @sql nvarchar(max);

DECLARE @sql_column nvarchar(max);

DECLARE @is_nullable int;

DECLARE @nullornot nvarchar(255);

SET @collate = 'Chinese_PRC_CS_AS'; --不区分大小写：  Chinese_PRC_CI_AS    区分大小写 ：Chinese_PRC_CS_AS

DECLARE local_table_cursor CURSOR FOR

SELECT [name]

FROM sysobjects

WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor

FETCH NEXT FROM local_table_cursor

INTO @table

WHILE @@FETCH_STATUS = 0

BEGIN

    DECLARE local_change_cursor CURSOR FOR

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id

        , c.name column_name

        , t.Name data_type

        , c.max_length

        , c.column_id

		, c.is_nullable

    FROM sys.columns c

    JOIN sys.types t ON c.system_type_id = t.system_type_id

    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id

    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id

    WHERE c.object_id = OBJECT_ID(@table)

    ORDER BY c.column_id

    OPEN local_change_cursor

    FETCH NEXT FROM local_change_cursor

    INTO @row_id, @column_name, @data_type, @max_length, @column_id,@is_nullable

    WHILE @@FETCH_STATUS = 0

    BEGIN

        IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;

		IF (@is_nullable = 1) SET @nullornot=' not null' ELSE SET @nullornot=''

        IF (@data_type LIKE '%char%')

        BEGIN TRY

            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate  +' ' +@nullornot

            PRINT @sql

            EXEC sp_executesql @sql

        END TRY

        BEGIN CATCH

          PRINT 'ERROR: Some index or constraint rely on the column ' + @column_name + '. No conversion possible.'

          PRINT @sql

        END CATCH

        FETCH NEXT FROM local_change_cursor

        INTO @row_id, @column_name, @data_type, @max_length, @column_id,@is_nullable

    END

    CLOSE local_change_cursor

    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor

    INTO @table

END

CLOSE local_table_cursor

DEALLOCATE local_table_cursor

GO