You can use a case sensitive collation like "COLLATE SQL_Latin1_General_CP1_CS_AS" to perform comparisons.
You're going to need to be more specific if you want a solution.
Do you want all rows in a table where a column contains any capital letters?
or where ALL letters are upper case?
Or maybe from a single value you want only the characters that are upper calse alpha characters returned?
This example will return only the row where ALL the letters are upper case:
select 'tg' as f union all
select 'Tg' union all
where f COLLATE SQL_Latin1_General_CP1_CS_AS = upper(f) COLLATE SQL_Latin1_General_CP1_CS_AS
Be One with the Optimizer