Busquedas de texto Accent Insensitive (acentos) en SQL Server

Podemos configurar SQL Server para que las búsquedas de texto sean Accent Insensitive, esto es, que no distingan entre cafe y café por ejemplo. Para esto hay que cambiar el COLLATE de la columna de texto donde deseamos este funcionamiento a un COLLATE Accent Insensitive, como Latin1_General_CI_AI, mediante un ALTER:

alter table SYS_FIELDS_999 alter column TEXTCOLUMN varchar (50) COLLATE Latin1_General_CI_AI null

Hay que tener en cuenta que si la columna esta agregada a un índice de texto el ALTER va a dar error, por lo que hay que quitar la columna del índice de texto, hacer el ALTER y luego agregarla de nuevo.

exec sp_fulltext_column N'[dbo].[SYS_FIELDS_999]', N'TEXTCOLUMN', 'drop'
alter table SYS_FIELDS_999 alter column TEXTCOLUMN varchar (50) COLLATE Latin1_General_CI_AI null
exec sp_fulltext_column N'[dbo].[SYS_FIELDS_999]', N'TEXTCOLUMN', 'add', 0x0c0a

Como hacer esto columna por columna puede ser bastante engorroso, podemos ejecutar una consulta que nos devuelva el listado de columnas de texto de una tabla, y los 3 comandos para cambiar el COLLATE

select 'exec sp_fulltext_column N''[dbo].[' + TABLE_NAME + ']'', N''' + COLUMN_NAME + ''', ''drop''' + char(13) +
'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' ' + DATA_TYPE + 
convert(varchar(30), case when DATA_TYPE = 'text' then '' else case when CHARACTER_MAXIMUM_LENGTH = -1 then ' (MAX)' else ' (' + convert(varchar(50), CHARACTER_MAXIMUM_LENGTH) + ')' end end) + 
' COLLATE Latin1_General_CI_AI ' + case when IS_NULLABLE = 'NO' then 'not null' else 'null' end + char(13) +
'exec sp_fulltext_column N''[dbo].[' + TABLE_NAME + ']'', N''' + COLUMN_NAME + ''', ''add'', 0x0c0a' ALTERQRY,
COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, IS_NULLABLE
from information_schema.columns
where TABLE_NAME = 'SYS_FIELDS_999' and (Data_Type LIKE '%char%' OR Data_Type LIKE '%text%')
ORDER BY ordinal_position 

Otra opción es eliminar el indice de texto completo de la tabla, cambiar el collate de las columnas y luego volver a crearlo. Para eliminar el indice de texto de una tabla:

DROP FULLTEXT INDEX ON [dbo].[SYS_FIELDS_999]

Para cambiar los collate de todas las columnas podemos usar una versión simplificada del query anterior:

select 'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' ' + DATA_TYPE + 
convert(varchar(30), case when DATA_TYPE = 'text' then '' else case when CHARACTER_MAXIMUM_LENGTH = -1 then ' (MAX)' else ' (' + convert(varchar(50), CHARACTER_MAXIMUM_LENGTH) + ')' end end) + 
' COLLATE Latin1_General_CI_AI ' + case when IS_NULLABLE = 'NO' then 'not null' else 'null' end ALTERQRY,
COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME, IS_NULLABLE
from information_schema.columns
where TABLE_NAME = 'SYS_FIELDS_432' and (Data_Type LIKE '%char%' OR Data_Type LIKE '%text%')
ORDER BY ordinal_position 

Y para volver a indexar todas las columnas de texto de la tabla podemos usar FULLTEXT_CREATEINDEX

Para listar el COLLATE de todas las columnas de la base:

SELECT t.Name 'Table', c.name 'Col', ty.name 'Type', c.max_length, c.collation_name, c.is_nullable
FROM sys.columns c INNER JOIN sys.tables t
ON c.object_id = t.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id    
WHERE t.is_ms_shipped = 0

Para cambiar el collate de la BASE DE DATOS, hay que cambiar el modo de la base a SINGLE_USER, quitar los contraints que dependen del collate, cambiar el collate, crear de nuevo los contraints, y por último volver la base a MULLTI_USER:

-- Cambia a SINGLE_USER
ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [myDB]
GO

-- Quita los contraints que dependen del collate
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_FOLDERS_INHERITS]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_FOLDERS]'))
ALTER TABLE [dbo].[SYS_FOLDERS] DROP CONSTRAINT [CHK_FOLDERS_INHERITS]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_EVENTS_RECURSIVE]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_EVENTS]'))
ALTER TABLE [dbo].[SYS_EVENTS] DROP CONSTRAINT [CHK_EVENTS_RECURSIVE]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_ACCOUNTS_ACC_ID_TYPE]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_ACCOUNTS]'))
ALTER TABLE [dbo].[SYS_ACCOUNTS] DROP CONSTRAINT [CHK_ACCOUNTS_ACC_ID_TYPE]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_ACC_REL_ACC_ID_PARENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_ACC_REL]'))
ALTER TABLE [dbo].[SYS_ACC_REL] DROP CONSTRAINT [CHK_ACC_REL_ACC_ID_PARENT]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_ACC_USERS_ACC_ID]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_ACC_USERS]'))
ALTER TABLE [dbo].[SYS_ACC_USERS] DROP CONSTRAINT [CHK_ACC_USERS_ACC_ID]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_ACC_USERS_DISABLED]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_ACC_USERS]'))
ALTER TABLE [dbo].[SYS_ACC_USERS] DROP CONSTRAINT [CHK_ACC_USERS_DISABLED]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CHK_EVENTS_NET_RECURSIVE]') AND parent_object_id = OBJECT_ID(N'[dbo].[SYS_EVENTS_NET]'))
ALTER TABLE [dbo].[SYS_EVENTS_NET] DROP CONSTRAINT [CHK_EVENTS_NET_RECURSIVE]
GO

-- Cambia el collate
ALTER DATABASE myDB COLLATE Latin1_General_CI_AI
GO

-- Reestablece los contraints
ALTER TABLE [dbo].[SYS_FOLDERS]  WITH CHECK ADD  CONSTRAINT [CHK_FOLDERS_INHERITS] CHECK  (([INHERITS] = 0 or [PARENT_FOLDER] is not null))
GO

ALTER TABLE [dbo].[SYS_FOLDERS] CHECK CONSTRAINT [CHK_FOLDERS_INHERITS]
GO

ALTER TABLE [dbo].[SYS_EVENTS]  WITH CHECK ADD  CONSTRAINT [CHK_EVENTS_RECURSIVE] CHECK  (([RECURSIVE] = 0 or [TYPE] = 1))
GO

ALTER TABLE [dbo].[SYS_EVENTS] CHECK CONSTRAINT [CHK_EVENTS_RECURSIVE]
GO

ALTER TABLE [dbo].[SYS_ACCOUNTS]  WITH CHECK ADD  CONSTRAINT [CHK_ACCOUNTS_ACC_ID_TYPE] CHECK  (([TYPE] = 1 and [ACC_ID] >= 0 and [ACC_ID] < 1000000 or [TYPE] = 2 and [ACC_ID] >= 1000000 or [TYPE] = 3 and [ACC_ID] < 0))
GO

ALTER TABLE [dbo].[SYS_ACCOUNTS] CHECK CONSTRAINT [CHK_ACCOUNTS_ACC_ID_TYPE]
GO

ALTER TABLE [dbo].[SYS_ACC_REL]  WITH CHECK ADD  CONSTRAINT [CHK_ACC_REL_ACC_ID_PARENT] CHECK  (([ACC_ID_PARENT] >= 1000000))
GO

ALTER TABLE [dbo].[SYS_ACC_REL] CHECK CONSTRAINT [CHK_ACC_REL_ACC_ID_PARENT]
GO

ALTER TABLE [dbo].[SYS_ACC_USERS]  WITH CHECK ADD  CONSTRAINT [CHK_ACC_USERS_ACC_ID] CHECK  (([ACC_ID] >= 0 and [ACC_ID] < 1000000))
GO

ALTER TABLE [dbo].[SYS_ACC_USERS] CHECK CONSTRAINT [CHK_ACC_USERS_ACC_ID]
GO

ALTER TABLE [dbo].[SYS_ACC_USERS]  WITH CHECK ADD  CONSTRAINT [CHK_ACC_USERS_DISABLED] CHECK  (([ACC_ID] <> 0 or [ACC_ID] = 0 and [DISABLED] = 0))
GO

ALTER TABLE [dbo].[SYS_ACC_USERS] CHECK CONSTRAINT [CHK_ACC_USERS_DISABLED]
GO

ALTER TABLE [dbo].[SYS_EVENTS_NET]  WITH CHECK ADD  CONSTRAINT [CHK_EVENTS_NET_RECURSIVE] CHECK  (([RECURSIVE] = 0 or [TYPE] = 1))
GO

ALTER TABLE [dbo].[SYS_EVENTS_NET] CHECK CONSTRAINT [CHK_EVENTS_NET_RECURSIVE]
GO

-- Vuelve la base a MULTI_USER
ALTER DATABASE myDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO