sqlserver查看数据库下所有index,每条index占一行,有index名,表名,index包含的列名,每个索引包含的列名只占一行,排除主键索引
时间: 2024-01-25 21:13:57 浏览: 32
您可以使用以下 SQL 查询来获取 SQL Server 数据库中所有非主键索引的信息:
```
SELECT
i.name AS IndexName,
OBJECT_NAME(i.object_id) AS TableName,
STUFF((SELECT ', ' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c
ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
ORDER BY ic.index_column_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.indexes i
WHERE i.is_primary_key = 0
```
这将返回索引名称、表名称和索引包含的列名称的信息。每个索引将在单独的行上列出,并且排除了主键索引。