返回索引列名称。

语法

INDEX_COL ( 'table' , index_id , key_id )

参数

'table'

表的名称。

index_id

索引的 ID。

key_id

键的 ID。

返回类型

nvarchar (256)

示例

下面的示例将产生 authors 表中索引的列表。

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

下面是结果集：

Index information for the authors table
---------------------------------------
 
 Index number 1 is UPKCL_auidind
 Index number 2 is aunmind