Equivalent query in sql server for INFORMATION_SCHEMA

Question

I am working on a page where we need to visually compare the schema of one table between two databases - one in the sql server and the other in mysql.I should also include indexes.

now mysql query shows information 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 a query to compile the result -

  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, as things like filtered indexes and included columns are not part of the definition. I will talk more about this here:

Case Against INFORMATION_SCHEMA Submissions

For this you want to use sys.indexes and sys.index_columns . For instance:

 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; 

You did not specify the version of SQL Server that you are using, so here are all the links to topics about sys.indexes and sys.index_columns :

sys.indexes

SQL Server 2005 http://technet.microsoft.com/en-us/library/ms173760%28SQL.90%29.aspx
SQL Server 2008 http://technet.microsoft.com/en-us/library/ms173760%28SQL.100%29.aspx
SQL Server 2008 R2 http://technet.microsoft.com/en-us/library/ms173760%28SQL.105%29.aspx
SQL Server 2012 http://technet.microsoft.com/en-us/library/ms173760%28SQL.110%29.aspx

sys.index_columns

SQL Server 2005 http://technet.microsoft.com/en-us/library/ms175105%28SQL.90%29.aspx
SQL Server 2008 http://technet.microsoft.com/en-us/library/ms175105%28SQL.100%29.aspx
SQL Server 2008 R2 http://technet.microsoft.com/en-us/library/ms175105%28SQL.105%29.aspx
SQL Server 2012 http://technet.microsoft.com/en-us/library/ms175105%28SQL.110%29.aspx

You can also take a look at Kimberley L. Tripp sp_helpindex2 .

---

EDIT

In general, I agree with the comment by @BrianWhite. If you spend any effort on it at all, you should use the tool for this instead of reinventing the wheel and trying to write it yourself. Eliminating this one request, which you probably already spent, in terms of time, on the cost of a good tool. Please read this post:

http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/

Answer 2

Perhaps you are after sp_help

http://msdn.microsoft.com/en-us/library/ms187335.aspx

Just enter

 sp_help myTableName 

should provide you with all the necessary data.