Equivalent query in sql server for INFORMATION_SCHEMA

Question

I am working on a page where we need to visually compare the schema of same table across the two database-one in sql server and other in mysql.I have to include indexes as well.

now mysql query shows info along with indexes -

select column_name,column_type,table_name,column_key 
  from INFORMATION_SCHEMA.COLUMNS where table_name = 'tbl_ClientDN'

But for sql server the same query does not return indexes-

 select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'tbl_ClientDN'

so i need query to compbine the result of -

 sp_helpindex 'tbl_ClientDN'

how to get column_key showing indexes in mssql query. any suggestion ?

Answer 1

Stay away from INFORMATION_SCHEMA.COLUMNS, especially for indexes, since things like filtered indexes and included columns are not part of the definition. I talk about this in more detail here:

• The case against INFORMATION_SCHEMA views

You want to use sys.indexes and sys.index_columns for this. For example:

DECLARE @tablename NVARCHAR(512) = 'dbo.tbl_ClientDN';

SELECT
    [Index]  = i.name,
    [Column] = c.Name, 
    [Type]   = i.type_desc,
    PK = i.is_primary_key,
    [Unique] = i.is_unique,
    [Unique Constraint] = i.is_unique_constraint,
    [DESC] = ic.is_descending_key,
    [INCLUDE] = ic.is_included_column,
    [Filtered] = i.filter_definition -- only for SQL Server 2008+
FROM
    sys.indexes AS i 
INNER JOIN 
    sys.index_columns AS ic 
    ON i.[object_id] = ic.[object_id] 
    AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c
    ON ic.column_id = c.column_id
    AND ic.[object_id] = c.[object_id]
WHERE 
    i.[object_id] = OBJECT_ID(@tablename)
ORDER BY [Index], ic.index_column_id;

If you want to do this for all tables at once, then simple changes:

SELECT
    [Table] = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
      + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    [Index]  = i.name,
    [Column] = c.Name, 
    [Type]   = i.type_desc,
    PK = i.is_primary_key,
    [Unique] = i.is_unique,
    [Unique Constraint] = i.is_unique_constraint,
    [DESC] = ic.is_descending_key,
    [INCLUDE] = ic.is_included_column,
    [Filtered] = i.filter_definition -- only for SQL Server 2008+
FROM
    sys.indexes AS i 
INNER JOIN 
    sys.index_columns AS ic 
    ON i.[object_id] = ic.[object_id] 
    AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c
    ON ic.column_id = c.column_id
    AND ic.[object_id] = c.[object_id]
ORDER BY [Table], [Index], ic.index_column_id;

More information available in the topics sys.indexes and sys.index_columns.

You also might want to take a look at Kimberley L. Tripp's sp_helpindex2.

---

EDIT

In general I agree with @BrianWhite's comment. If you are spending any effort on this at all, you should be using a tool for this instead of re-inventing the wheel and trying to write it yourself. Troubleshooting this one query you've probably already spent, in terms of time, the cost of a good tool. Please read this post:

• The cost of reinventing the wheel