How to check for lower or upper case case in Transact-SQL

A colleague of mine needs to extract data from a table where one field has lower case characters (any lower case charcater). Logically this would be the obvious solution...

SELECT my_field FROM my_table where myfield <> UPPER(my_field)

But normally with T-SQL it never yelds results becasuse the default collation is case insensitive. But one can use the COLLATE keyword and use a suitable case sensitive collation order...

SELECT my_field FROM my_table where myfield <> UPPER(my_field) COLLATE Latin1_General_CS_AI

Use this to list the available collation tags..

SELECT * FROM fn_helpcollations()