当我有这个需求时，我使用了以下查询…

SELECT 
    TableName = t.name,
    ColumnId = col.column_id, 
    ColumnName = col.name,
    DataType = ty.name,
    MaxSize = ty.max_length,
    IsNullable = CASE WHEN (col.is_nullable = 1) THEN 'Y' END,
    IsIdentity = CASE WHEN (col.is_identity = 1) THEN 'Y' END,
    IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN 'Y' END,
    IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN 'Y' END,
    IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN 'Y' END
FROM 
    sys.tables t
INNER JOIN 
     sys.columns col ON t.object_id = col.object_id 
LEFT JOIN
    sys.indexes ind ON t.object_id = ind.object_id 
LEFT JOIN 
     sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
                ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
                ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
     sys.types ty on ty.user_type_id = col.user_type_id

WHERE
    --t.name='<TABLENAME>'
    t.schema_id = 10    --SCHEMA ID
    AND ind.is_primary_key=1    
ORDER BY
    t.name, ColumnId

以下是最好的方法:

SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key 
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns 
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id) 
AND sys.tables.name = 'your_table_name'

我更喜欢使用隐式连接，因为它对我来说更容易理解。您可以删除object_id引用，因为您可能不需要它。

欢呼。

当我有这个需求时，我使用了以下查询…

SELECT 
    TableName = t.name,
    ColumnId = col.column_id, 
    ColumnName = col.name,
    DataType = ty.name,
    MaxSize = ty.max_length,
    IsNullable = CASE WHEN (col.is_nullable = 1) THEN 'Y' END,
    IsIdentity = CASE WHEN (col.is_identity = 1) THEN 'Y' END,
    IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN 'Y' END,
    IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN 'Y' END,
    IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN 'Y' END
FROM 
    sys.tables t
INNER JOIN 
     sys.columns col ON t.object_id = col.object_id 
LEFT JOIN
    sys.indexes ind ON t.object_id = ind.object_id 
LEFT JOIN 
     sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
                ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
                ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
     sys.types ty on ty.user_type_id = col.user_type_id

WHERE
    --t.name='<TABLENAME>'
    t.schema_id = 10    --SCHEMA ID
    AND ind.is_primary_key=1    
ORDER BY
    t.name, ColumnId

在Oracle中

select CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME,CONNECYBY.COLUMN_NAME
from (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,COLUMN_POSITION,trim(',' from sys_connect_by_path(COLUMN_NAME,',')) COLUMN_NAME
        from DBA_IND_COLUMNS
        start with COLUMN_POSITION = 1
        connect by TABLE_OWNER = prior TABLE_OWNER
        and TABLE_NAME = prior TABLE_NAME
        and INDEX_NAME = prior INDEX_NAME
        and COLUMN_POSITION = prior COLUMN_POSITION + 1) CONNECYBY
join (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,max(COLUMN_POSITION) COLUMN_POSITION
        from DBA_IND_COLUMNS
        group by TABLE_OWNER,TABLE_NAME,INDEX_NAME) MAX_CONNECYBY
on (    CONNECYBY.SCHEMA_NAME = MAX_CONNECYBY.SCHEMA_NAME
        and CONNECYBY.TABLE_NAME = MAX_CONNECYBY.TABLE_NAME
        and CONNECYBY.INDEX_NAME = MAX_CONNECYBY.INDEX_NAME
        and CONNECYBY.COLUMN_POSITION = MAX_CONNECYBY.COLUMN_POSITION)
order by CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME

SQL Server 与

CONNECTBY(SCHEMA_NAME,TABLE_NAME,INDEX_NAME,INDEX_COLUMN_ID,COLUMN_NAME) 
as 
    (   select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        where INDEX_COLUMNS.INDEX_COLUMN_ID = 1
        union all
        select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(PRIOR.COLUMN_NAME + ',' + COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        join CONNECTBY as PRIOR on (SCHEMAS.NAME = PRIOR.SCHEMA_NAME 
                                    and TABLES.NAME = PRIOR.TABLE_NAME 
                                    and INDEXES.NAME = PRIOR.INDEX_NAME 
                                    and INDEX_COLUMNS.INDEX_COLUMN_ID = PRIOR.INDEX_COLUMN_ID + 1))
select CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME,CONNECTBY.COLUMN_NAME
from CONNECTBY
join (  select  SCHEMA_NAME
                , TABLE_NAME
                , INDEX_NAME
                , MAX(INDEX_COLUMN_ID) INDEX_COLUMN_ID
        from CONNECTBY 
        group by SCHEMA_NAME,TABLE_NAME,INDEX_NAME) MAX_CONNECTBY
        on (CONNECTBY.SCHEMA_NAME = MAX_CONNECTBY.SCHEMA_NAME
            and CONNECTBY.TABLE_NAME = MAX_CONNECTBY.TABLE_NAME
            and CONNECTBY.INDEX_NAME = MAX_CONNECTBY.INDEX_NAME
            and CONNECTBY.INDEX_COLUMN_ID = MAX_CONNECTBY.INDEX_COLUMN_ID)
order by CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME

我没有经过，但是我在原作者发布的查询中得到了我想要的东西。

我使用它(没有条件/过滤器)来满足我的需求，但它给出了不正确的结果

主要问题是在index_id上没有连接条件的情况下得到叉乘

SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME COLUMN_NAME
  FROM SYS.TABLES T
       INNER JOIN SYS.SCHEMAS S
    ON T.SCHEMA_ID = S.SCHEMA_ID
       INNER JOIN SYS.INDEXES I
    ON I.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.INDEX_COLUMNS IC
    ON IC.OBJECT_ID = T.OBJECT_ID
       INNER JOIN SYS.COLUMNS C
    ON C.OBJECT_ID  = T.OBJECT_ID
   **AND IC.INDEX_ID    = I.INDEX_ID**
   AND IC.COLUMN_ID = C.COLUMN_ID
 WHERE 1=1

ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL

既然你的配置文件声明你使用的是。net，那么你可以通过编程的方式使用服务器管理对象(SMO)…除此之外，上面的任何答案都非常棒。