Returns the indexed column name.
INDEX_COL('table', index_id, key_id)
nchar
This example produces a list of indexes in the authors table.
USE pubs
-- Declare variables to use in this example.
DECLARE @id int, @type char(2),@msg varchar(80),
@indid smallint, @indname sysname, @status int,
@indkey int, @name varchar(30)
-- Obtain the identification number for the authors table to look up
-- its indexes in the sysindexes table.
SET NOCOUNT ON
SELECT @id = id, @type = type
FROM sysobjects
WHERE name = 'authors' and type = 'U'
-- Start printing the output information.
print 'Index information for the authors table'
print '---------------------------------------'
-- Loop through all indexes in the authors table.
-- Declare a cursor.
DECLARE i cursor
FOR
SELECT indid, name, status
FROM sysindexes
WHERE id = @id
-- Open the cursor and fetch next set of index information.
OPEN i
FETCH NEXT FROM i INTO @indid, @indname, @status
IF @@FETCH_STATUS = 0
PRINT ' '
-- While there are still rows to retrieve from the cursor,
-- find out index information and print it.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg = NULL
-- Print the index name and the index number.
SET @msg = ' Index number ' + CONVERT(varchar, @indid)+
' is '+@indname
SET @indkey = 1
-- @indkey (equivalent to key_id in the syntax diagram of
-- INDEX_COL) can be from 1 to 16.
WHILE @indkey <= 16 and INDEX_COL(@name, @indid, @indkey)
IS NOT NULL
BEGIN
-- Print different information if @indkey <> 1.
IF @indkey = 1
SET @msg = @msg + ' on '
+ index_col(@name, @indid, @indkey)
ELSE
SET @msg = @msg + ', '
+ index_col(@name, @indid, @indkey)
SET @indkey = @indkey + 1
END
PRINT @msg
SET @msg = NULL
FETCH NEXT FROM i INTO @indid, @indname, @status
END
CLOSE i
DEALLOCATE i
SET NOCOUNT OFF
Here is the result set:
Index information for the authors table
---------------------------------------
Index number 1 is UPKCL_auidind
Index number 2 is aunmind
| Expressions | Metadata Functions |
| WHERE |