Modificar collations de tabelas do SQL Server

-- VERIFICANDO COLLACTION DAS TABELAS
-- CHECKING COLLATION OF TABLES
SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
order by a.name;
 
-- SQL SERVER 2000
 
-- VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS
-- CHECK COLLATION OF ONE OR ALL TABLES
 
SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE 't'– FILTRO NOME DA TABELA
order by a.name
 
-- VERIFICAR COLLATION DE UMA OU TODAS AS TABELAS
-- CHECK COLLATION OF ONE OR ALL TABLES
SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
AND a.name LIKE 't'– FILTRO
order by a.name
 
-- ALTERAR
DECLARE @tabela VARCHAR(100)
DECLARE @collate VARCHAR(100)
–SET @tabela='t'
SET @collate='SQL_Latin1_General_CP1_CI_AI' — SQL_Latin1_General_CP1_CI_AI
SELECT 'Alter Table '+ a.name + ' Alter Column '+ b.name +' '+c.name +' ('+CONVERT(VARCHAR(50),b.length)+') Collate'+' '+@collate
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join systypes C on C.xtype = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation not like 'SQL_Latin1_General_CP1_CI_AI'
--AND a.name=@tabela — FILTRO
order by a.name
 
-- COMPROVANDO QUE NÃO EXISTEM TABELAS COM COLLATION DIFERENTE DO SOLICITADO 
-- PROVING THAT THERE ARE NO TABLES WITH A COLLATION DIFFERENT FROM THE REQUESTED
 
SELECT
a.name as 'NomeTabela' ,
b.name as 'NomeColuna',
b.collation ,
c.name as 'TipoDados',
b.length as 'Tamanho'
FROM sysobjects A
inner join syscolumns B on a.id = b.id
inner join sys.types C on C.system_type_id = b.xtype
WHERE a.xType = 'U'
and b.collation is not null
and ((c.name = 'char') or (c.name = 'varchar'))
and a.name in (SELECT table_name FROM information_schema.tables)
and b.collation !='SQL_Latin1_General_CP1_CI_AI'
order by a.name