Comme il est important de savoir si la collation utilisée par une colonne, une base de données ou une instance SQL Server est sensible à la casse ou aux accents, ou …, voici quelques requêtes qui permettent d’obtenir l’information très simplement :
=> Au niveau colonne :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ------------------------------- -- Nicolas Souquet - 27/08/2011 ------------------------------- SELECT S.name + '.' + T.name AS table_name , C.name AS column_name , C.collation_name AS column_collation_name , FHC.description AS collation_description FROM sys.columns AS C INNER JOIN sys.tables AS T ON T.object_id = C.object_id INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id INNER JOIN sys.fn_helpcollations() AS FHC ON C.collation_name = FHC.name WHERE S.name = 'dbo' AND T.name = 'maTable' AND C.name = 'maColonne' |
=> Au niveau base de données :
1 2 3 4 5 6 7 8 9 10 | ------------------------------- -- Nicolas Souquet - 27/08/2011 ------------------------------- SELECT D.name AS database_name , D.collation_name AS database_collation_name , FHC.description AS collation_description FROM sys.databases AS D INNER JOIN sys.fn_helpcollations() AS FHC ON D.collation_name = FHC.name --WHERE D.name = 'leNomDeVotreBaseDeDonnees' |
=> Au niveau de l’instance SQL Server
1 2 3 4 5 6 7 8 | ------------------------------- -- Nicolas Souquet - 27/08/2011 ------------------------------- SELECT SC.server_collation_name , FHC.description AS server_collation_name FROM (SELECT SERVERPROPERTY('Collation') AS server_collation_name) AS SC INNER JOIN sys.fn_helpcollations() AS FHC ON SC.server_collation_name = FHC.name |
Pour en savoir un peu plus sur les collations dans SQL Server, je vous conseille de lire l’article de SQLPro à ce sujet.
Bon tri de chaînes !
Bonjour Nicolas,
Il me semble qu’il y a une coquille dans la requête collation niveau colonne.
Prenons un exemple :
–>Pour une collation serveur French_CI_AS
2
3
4
5
6
7
SELECT SERVERPROPERTY('CollationID') [IDCollationServer],SERVERPROPERTY('Collation' [CollationServer] <br />
<br />
IDCollationServer CollationServer <br />
---------------- -------------------- <br />
<br />
53259 French_CI_AS <br />
–> Et une base de données de collation Latin1_General_CS_AS
2
3
4
5
6
7
8
SELECT name [Base], collation_name [CollationBase] FROM sys.databases WHERE name = 'AdventureWorks' <br />
<br />
<br />
Base CollationBase <br />
---- ----------------- <br />
AdventureWorks Latin1_General_CS_AS <br />
<br />
L’exécution de la requête suivante :
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT S.name + '.' + T.name AS table_name <br />
, C.name AS column_name <br />
, C.collation_name AS column_collation_name <br />
, FHC.description AS collation_description <br />
FROM sys.columns AS C <br />
INNER JOIN sys.tables AS T <br />
ON T.object_id = C.object_id <br />
INNER JOIN sys.schemas AS S <br />
ON S.schema_id = T.schema_id <br />
INNER JOIN sys.fn_helpcollations() AS FHC <br />
ON C.collation_name = FHC.name <br />
<br />
WHERE S.name = 'Person' <br />
AND T.name = 'Contact' <br />
Renvoie une erreur classique connue :
Msg 468, Niveau 16, État 9, Ligne 11
Impossible de résoudre le conflit de classement entre « French_CI_AS » et « Latin1_General_CS_AS » dans l’opération equal to.
–> Contournement
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT S.name + '.' + T.name AS table_name <br />
, C.name AS column_name <br />
, C.collation_name AS column_collation_name <br />
, FHC.description AS collation_description <br />
FROM sys.columns AS C <br />
INNER JOIN sys.tables AS T <br />
ON T.object_id = C.object_id <br />
INNER JOIN sys.schemas AS S <br />
ON S.schema_id = T.schema_id <br />
INNER JOIN sys.fn_helpcollations() AS FHC <br />
ON C.collation_name = FHC.name COLLATE database_default <br />
<br />
WHERE S.name = 'Person' <br />
AND T.name = 'Contact' <br />
<br />
<code> <br />
<br />
<br />
<br />
<br />