Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Unanswered: Syscolumns and Sysindexes Join Problem

    Help !"!!!

    I have come accross a problem when tring to use the system table in SQL Server 2000.

    I am trying to assess if a particular column has an index on it and if it has then get the name of the index.

    I can see how to join the sysobjects with the sysindexes to get all the indexes for a table.

    What i want to do is find if a specific column has an index on it by joining the syscolumns table with the sysindexes table.

    Problem is I can't see how to do it.

    Is there a way to assess if a specific column has an index on it

    Help !!!!

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    select distinct o.[name] as table_name, i.[name] as index_name, c.[name] as column_name
    from [dbo].[sysindexes] i
    inner join [dbo].[sysindexkeys] k
    on i.[id] = k.[id]
    and i.[indid] = k.[indid]
    inner join [dbo].[sysobjects] o
    on o.[id] = i.[id]
    inner join [dbo].[syscolumns] c
    on c.[id] = o.[id]
    and c.[colid] = k.[colid]
    where INDEXPROPERTY( o.[id], i.[name], 'IsStatistics' ) = 0

    NOTE: the distinct ensures that columns used in more than one index occur only once in the result set, and that indexproperty() is used to exclude statistics.

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking

    Excellent stuff mate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •