Modificando Collate SQL Server

— VERIFICANDO COLLACTION DAS TABELAS
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

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

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 

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

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s