Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#80 MSSQL - Error in gathering index information

3.0.0
closed-fixed
Jakub Vrána
MS SQL (15)
5
2010-11-26
2010-10-15
Anonymous
No

Adminer with MS SQL Server 2005 uses following query to gather index informations:
SELECT indexes.name, key_ordinal, is_unique, is_primary_key, columns.name AS column_name
FROM sys.indexes
INNER JOIN sys.index_columns ON indexes.object_id = index_columns.object_id AND indexes.index_id = index_columns.index_id
INNER JOIN sys.columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id
WHERE OBJECT_NAME(indexes.object_id) = 'table_name'

Query returns these errors:
The multi-part identifier "indexes.object_id" could not be bound.
The multi-part identifier "index_columns.object_id" could not be bound.
The multi-part identifier "indexes.index_id" could not be bound.
The multi-part identifier "index_columns.index_id" could not be bound.
The multi-part identifier "index_columns.object_id" could not be bound.
The multi-part identifier "columns.object_id" could not be bound.
The multi-part identifier "index_columns.column_id" could not be bound.
The multi-part identifier "columns.column_id" could not be bound.
The multi-part identifier "indexes.object_id" could not be bound.
The multi-part identifier "indexes.name" could not be bound.
The multi-part identifier "columns.name" could not be bound.

Correct query (version A):
SELECT sys.indexes.name, key_ordinal, is_unique, is_primary_key, sys.columns.name AS column_name
FROM sys.indexes
INNER JOIN sys.index_columns ON sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id
INNER JOIN sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id
WHERE OBJECT_NAME(sys.indexes.object_id) = 'table_name'

Correct query (version B):
SELECT i.name, key_ordinal, is_unique, is_primary_key, c.name AS column_name
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'table_name'

Discussion

  • Jakub Vrána
    Jakub Vrána
    2010-10-16

    • status: open --> closed-fixed
     
  • Jakub Vrána
    Jakub Vrána
    2010-10-16

    Fixed in Git, thank you for the report. Can you please confirm if Adminer works with MS SQL 2005?

     
  • Thanks for fixing. Adminer shows correct index information.

     
  • Jakub Vrána
    Jakub Vrána
    2010-11-26

    • labels: 1212738 --> MS SQL