SQL SERVER – Find Collation of Database and Table Column Using T-SQL
Today we will go over very quick tip about finding out collation of database and table column. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales
Today’s script are self explanatory so I will not explain it much.
/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO
/* Find Collation of SQL Server Database Table Column */
USE AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND name = 'City'
[画像:SQL SERVER - Find Collation of Database and Table Column Using T-SQL findcollation ]
Reference : Pinal Dave (https://blog.sqlauthority.com )
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 14 SQL Server database books and 98 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.
Comprehensive Database Performance Health Check
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
SQL Server Performance Tuning Practical Workshop
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
13 Comments. Leave new
You can do the same with
select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name
Yeah, this works! Thank you!
Thank You very much…
Hmm,
Anyone else get this error when attempting to execute the output from script 3?:
ERROR: Load object, dump buffer.
ERROR: Load object, dump buffer.
I too got the following error for 3rd script (script to add dependencies) ERROR: Load object, dump buffer.
I know this is late, but it will help someone in future…We receive this error if the sequence is not followed properly during the execution. we see this error when step 14 is accidentally skipped.
Sir, Let suppose i have a column name “something_contact” in a table of database. Now i want to make a filter that will show me all the columns which contains “contact”. Remember it should be column not table containing “contact”. Is it posible?
If i am getting you right, i think i you can use like clause:
SELECT name
FROM sys.columns
where name Like ‘%contact%;
Hi Pinal,
I have changed the rule to handle case sensitive by updating database to SQL_Latin1_General_CP1_CI_AS.It worked fine. But again after changing the rule to SQL_Latin1_General_CP1_CS_AS ,it is behaving case sensitive ..
HI Pinal,
We have a situation where we are upgrading all our databases to new collation but do not know how to change all columns, fields, tables to the new collation. Any suggestion will be appreciated.
Great article site
How can I do this for a table type @table, because im getting this error for a temporary table:
Msg 468, Level 16, State 9, Procedure SPPInteroperaSiniestroOrdenCompania, Line 227
Cannot resolve the collation conflict between “SQL_Latin1_General_CP850_CI_AS” and “SQL_Latin1_General_CP850_CI_AI” in the equal to operation.
useful thank you