Question

我在一页上工作,我们需要对两个数据库的表象进行直观的比较,一个数据库是板球服务器,另一个是神话。我还必须包括指数。

如今,我的心脏病和指数显示胎儿衰变。

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

但是,对于ql服务器来说,同样的问题并不包括返回指数。

 select * from INFORMATION_SCHEMA.COLUMNS where table_name =  tbl_ClientDN

因此,需要确定:

 sp_helpindex  tbl_ClientDN

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

Answer 1

远离<代码>INFORMATION_SCHEMA.COLUMNS,特别是在索引方面,因为诸如过滤指数和列入一栏等内容不属于定义的范围。我在此更详细地谈到这一点:

The case against INFORMATION_SCHEMA views

页: 1 例如:

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;

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql”rel=“nofollow noretinger”>sys.indexes 和

您也不妨对 Kimberley L. Tripp s sp_helpindex2 。

http://www.ohchr.org。

总的来说,我同意“Brian White s”的意见。如果你全力以赴,你就应该利用这一工具,而不是重新发明轮椅,并试图书写轮椅。或许你在时间上已经花掉了好工具的成本。请改为:

The cost of reinventing the wheel

Answer 2

或许请在<条码>中填写。

简单输入

sp_help myTableName

应向您提供您所需要的一切数据。

友情链接