finding index with column name 1

[JudyKadle]

I am new to SQL Server and this forum. I have learned a lot by searching and viewing already - thanks for that.

I am wondering how to find tables, columns whose column names match something like 'login' where those columns are used as a key or index. I know how to do part of that (see below), but not the key/index part. I may have to change the collation on some columns and do not what to do so for indices. Any assistance would be greatly appreciated.

Judy

select st.name as table_name, sc.name as column_name, sc.collation_name
from sys.columns sc,
sys.tables st
where st.object_id = sc.object_id
and sc.name like '%login%'

 

[fuzzyocelot]

It looks like you're using SQL 2005. Is that right? If so, try taking a look at the following catalog views to see if they're what you're looking for:

sys.foreign_keys
sys.foreign_key_columns
sys.indexes
sys.index_columns
sys.key_constraints