query to find index on a column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » query to find index on a column

query to find index on a column

Rate Topic

Display Mode

Topic Options

Author

Message

curious_sqldba

Posted Monday, October 08, 2012 4:36 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,178, Visits: 2,701

Is there a way i can find all the indices on a given column?

Post #1370101

GilaMonster

Posted Monday, October 08, 2012 5:10 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 38,091, Visits: 30,385

Sure. The catalog views involved are sys.indexes, sys.index_columns and sys.columns (and maybe sys.tables)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1370107

joyento.acs

Posted Wednesday, October 10, 2012 1:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 5:19 AM
Points: 44, Visits: 136

Hi,

If u want queried for indexes....

select name from sysindexes where object_id in (select object_id from systables or syscolumns)
Go

Post #1370730

GilaMonster

Posted Wednesday, October 10, 2012 1:29 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 38,091, Visits: 30,385

sysindexes and syscolumns are deprecated, should not be used any longer, will be removed from a future version of SQL Server. The replacements are sys.indexes and sys.columns.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1370735

« Prev Topic | Next Topic »

Permissions