Capturando a Collation de Todas as bases e Colunas da Instância

Recentemente fui incumbido de realizar um levantamento das collations das bases de dados e colunas do sistema transacional da ANEEL para que pudéssemos mensurar a complexidade de uma migração de Collation.

Com base nisso, desenvolvi uma query usando as DMVs do SQL Server que busca em cada um dos bancos de dados de uma instância sua collation e também quantidade de colunas que cada base possui com collation divergente da collation da base de dados.


DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'

SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify amount of tables, table text columns and table text columns with collation difference of database
 COUNT( DISTINCT tb_name) AS qtd_tables,
 COUNT(tb_columns_name) AS qtd_table_columns,
 SUM(CASE WHEN db_collation_name <> tb_column_collation_name THEN 1 ELSE 0 END) AS qtd_table_columns_with_different_collation,
 
 --verify amount of views, view text columns and view text columns with collation difference of database
 COUNT(DISTINCT vw_name) AS qtd_views,
 COUNT(vw_columns_name) AS qtd_view_columns,
 SUM(CASE WHEN db_collation_name <> vw_column_collation_name THEN 1 ELSE 0 END) AS qtd_view_columns_with_different_collation,

 --verify amount of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 COUNT(tvf_name) AS qtd_tvf,
 COUNT(tvf_columns_name) AS qtd_tvf_columns,
 SUM(CASE WHEN db_collation_name <> tvf_column_collation_name THEN 1 ELSE 0 END) AS qtd_tvf_columns_with_different_collation,

 --verify amount of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 COUNT(itf_name) AS qtd_itf,
 COUNT(itf_columns_name) AS qtd_itf_columns,
 SUM(CASE WHEN db_collation_name <> itf_column_collation_name THEN 1 ELSE 0 END) AS qtd_itf_columns_with_different_collation,
 
 --verify amount of table types, table type text columns and table type text columns with collation difference of database
 COUNT(tt_name) AS qtd_tt,
 COUNT(tt_columns_name) AS qtd_tt_columns,
 SUM(CASE WHEN db_collation_name <> tt_column_collation_name THEN 1 ELSE 0 END) AS qtd_tt_columns_with_different_collation

FROM @informations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
GROUP BY database_name,
 db_collation_name

Caso você queira saber quais os nomes das colunas cujo Collation diverge da collation da base de dados para cada objeto, basta usar a query abaixo:

DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'


SELECT * 
FROM (
 SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify name of tables, table text columns and table text columns with collation difference of database
 tb_name AS table_name,
 tb_columns_name AS table_column_name,
 CASE WHEN db_collation_name <> tb_column_collation_name THEN tb_column_collation_name ELSE NULL END AS table_column_collation,
 
 --verify name of views, view text columns and view text columns with collation difference of database
 vw_name AS view_name,
 vw_columns_name AS view_column_name,
 CASE WHEN db_collation_name <> vw_column_collation_name THEN vw_column_collation_name ELSE NULL END AS view_column_collation,

 --verify name of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 tvf_name AS tvf_name,
 tvf_columns_name AS tvf_column_name,
 CASE WHEN db_collation_name <> tvf_column_collation_name THEN tvf_column_collation_name ELSE NULL END AS tvf_column_collation,

 --verify name of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 itf_name AS itf_name,
 itf_columns_name AS itf_column_name,
 CASE WHEN db_collation_name <> itf_column_collation_name THEN itf_column_collation_name ELSE NULL END AS itf_column_collation,
 
 --verify name of table types, table type text columns and table type text columns with collation difference of database
 tt_name AS tt_name,
 tt_columns_name AS tt_column_name,
 CASE WHEN db_collation_name <> tt_column_collation_name THEN tt_column_collation_name ELSE NULL END AS tt_column_collation

 FROM @informations
) AS detail_infornmations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
 AND (table_column_collation IS NOT NULL 
 OR view_column_collation IS NOT NULL
 OR tvf_column_collation IS NOT NULL
 OR itf_column_collation IS NOT NULL
 OR tt_column_collation IS NOT NULL)

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