0

I wanted to write a query which would list all the tables which has a specific column and check the indexes of those tables to see whether a particular column is added to the index key column.

I have built the query to list all the tables with a specific column: Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'Date'

The result throws me about 100 tables.now i wanted to write a query to find if 'Date' column is added to their indexes as a index key column.

can someone help me out in this.

thanks in advance.

bl1234
  • 163
  • 7
  • http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db – Ric Jun 30 '15 at 10:03
  • Thanks for your reply..It was very helpful.but to be more specific i want to list out all the tables which don’t have Index with Date although they have column Date. – bl1234 Jun 30 '15 at 10:16
  • I think the answer to the post will lead you to what you want to achieve, but you will need to add/remove parts of it to get it working, but that shouldn't be a problem. – Ric Jun 30 '15 at 10:17
  • I added a additional where clause to specify the col.name='Date', but it does not give me a expected output.I wanted to see if the column 'Date' exist in table schema and then i want to flag out the tables that does not contain this column 'Date' in indexes.Could you please help – bl1234 Jul 01 '15 at 01:58

1 Answers1

0

Try this

Select * from sys.columns col 
left join sys.indexes_columns indxcols on indxcols.object_id = col.object_id and indxcols.column_id = col.column_id 
where col.name = 'Date' and indxcols.column_id is null
Azar
  • 1,852
  • 15
  • 17