system views sql

Question

Post reply

system views sql

newbieuser

SSCrazy Eights

Points: 9423
More actions
September 7, 2010 at 9:41 am

#239952

Hi all,
I'm trying to find the table columns that have wrong collation.. THe sql below gives the column names and the collation but I need the table names also.. Any help please?
SELECT c.name, c.collation_name
FROM sys.columns c
WHERE c.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects o
WHERE o.type = 'U')
GO
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Here is one way:

SELECT schema_name(so.schema_id) as SchemaName, object_name(so.object_id) as ObjectName,

c.name, c.collation_name

FROM sys.columns c inner join sys.objects so on c.object_id = so.object_id

WHERE so.type = 'U' and collation_name is not null

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SELECT Schema_Name(Schema_id) as SchemaName, t.name as TableName, c.name as ColumnName, c.collation_name

FROM sys.tables t INNER JOIN sys.columns c on t.object_id = c.object_id

WHERE collation_name is not null

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Thanks.. Is it possible to find the collation only for varchar fields???

Thank you so much again

WayneS SSC Guru Points: 95460 More actions · Answer 4

newbieuser (9/7/2010)
Thanks.. Is it possible to find the collation only for varchar fields???
Thank you so much again

SELECT ss.name as SchemaName,

t.name as TableName,

c.name as ColumnName,

c.collation_name,

st.name as DataType

FROM sys.tables t

JOIN sys.schemas ss ON ss.schema_id = t.schema_id

JOIN sys.columns c ON t.object_id = c.object_id

JOIN sys.types st ON st.system_type_id = c.system_type_id

WHERE c.collation_name is not null

AND st.name like '%char%' -- all character fields

--AND st.name = 'varchar' -- just varchar fields

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

drew.allen SSC Guru Points: 76985 More actions · Answer 5

You can also use the Information_Schema.Columns system view. It does all of the joins for you, and I find it much easier to remember one view.

SELECT Table_Schema, Table_Name, Column_Name, Collation_Name, Data_Type

FROM Information_Schema.Columns

WHERE Collation_Name IS NOT NULL

AND Data_Type = 'varchar'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA