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'

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

15 thoughts on “SQL SERVER – Find Collation of Database and Table Column Using T-SQL

  1. Pingback: SQL SERVER - Change Collation of Database Column - T-SQL Script Journey to SQL Authority with Pinal Dave

  2. 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.

    Like

      • 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.

        Like

  3. 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?

    Like

  4. Pingback: SQL SERVER – Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

  8. 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 ..

    Like

  9. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s