V_SCHEMA_INDEXCOLUMNS
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| TableName | nvarchar(128) | ||
| IndexOrdinal | int | ||
| IndexName | nvarchar(128) | yes | |
| ColumnOrdinal | tinyint | yes | |
| ColumnName | nvarchar(128) | yes | |
| IsDescending | bit | yes |
Definition
Copy
CREATE view dbo.V_SCHEMA_INDEXCOLUMNS
as
select sys.tables.Name as [TableName],
sys.indexes.Index_id as [IndexOrdinal],
sys.indexes.Name as [IndexName],
(case ic.key_ordinal when 0 then cast(1 as tinyint) else ic.key_ordinal end) as [ColumnOrdinal],
sys.columns.name as [ColumnName],
ic.is_descending_key as [IsDescending]
from
sys.tables
inner join sys.indexes on (sys.indexes.index_id > 0 and sys.indexes.is_hypothetical = 0) and (sys.indexes.object_id = sys.tables.object_id)
inner join sys.index_columns as ic on (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0) and is_included_column = 0) and
(ic.index_id = cast(sys.indexes.index_id as int) and ic.object_id = sys.indexes.object_id)
inner join sys.columns on sys.columns.object_id = ic.object_id and sys.columns.column_id = ic.column_id
where (SCHEMA_NAME(sys.tables.schema_id) = N'dbo')