mysql - sql server 中 INFORMATION_SCHEMA 的等效查询

Question

我正在处理一个页面，我们需要直观地比较两个数据库中同一个表的架构——一个在 sql server 中，另一个在 mysql 中。我还必须包括索引。

现在 mysql 查询显示信息和索引 -

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

但是对于sql server同样的查询不返回索引-

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

所以我需要查询来合并 -

 sp_helpindex 'tbl_ClientDN'

如何在 mssql 查询中获取显示索引的 column_key。有什么建议吗？

Answer 1

远离 INFORMATION_SCHEMA.COLUMNS，尤其是对于索引，因为过滤索引和包含的列之类的东西不是定义的一部分。我在这里更详细地讨论了这一点:

• The case against INFORMATION_SCHEMA views

你想为此使用 sys.indexes 和 sys.index_columns。例如:

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;

如果你想一次对所有表执行此操作，那么简单的更改:

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;

主题 sys.indexes 中提供了更多信息和 sys.index_columns .

您可能还想看看 Kimberley L. Tripp 的 sp_helpindex2 .

---

编辑

总的来说，我同意@BrianWhite 的评论。如果您为此付出任何努力，您应该为此使用工具，而不是重新发明轮子并尝试自己编写。就时间而言，对这个查询进行故障排除可能已经花费了一个好工具的成本。请阅读这篇文章:

• The cost of reinventing the wheel