Menu
How I can obtain the collation of a specific table in a database? Is it possible that a table have different collation in db?
772
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
Collation at the table level is on a per column basis, so it is possible to have a collation different than the database. If the collation is not defined at the column level, it defaults to the database collation setting.
SQL Server 2000:
SELECT c.name,
c.collation
FROM SYSCOLUMNS c
WHERE [id] = OBJECT_ID('your_table_name')
SQL Server 2005+:
SELECT c.name,
c.collation_name
FROM SYS.COLUMNS c
JOIN SYS.TABLES t ON t.object_id = c.object_id
WHERE t.name = 'your_table_name'
There is no such thing as a collation for a table.
A database has a default collation (which defaults to the collation for the server).
The default collation for the database will be applied to any column you add to a table, UNLESS you explicitly specify a collation at the column level.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
