怎么判断一个字段上是否存在索引?

[frogshero]

例如:
tableA(a int, b int, c int)
怎么知道是否有一个索引存在于a字段上而不是a,b2个字段上??

[duanduan1122]

1.sp_helpindex 'index_name' 就可以看到你的索引是在那列上了。

[duanduan1122]

上面错了
1.sp_helpindex 'table_name' 就可以看到你的那个索引是在那列上了。

[sindia]

UP

[talantlee]

sp_help '表名'
sp_helpindex '表名'


---查找到字段
select * from syscolumns where id =object_id('a') and colid in (select colid from sysindexkeys where id=object_id('a'))

[duanduan1122]

示例
下面的示例将产生 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, @name = 'authors'
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 on au_id
Index number 2 is aunmind on au_lname, au_fname

[duanduan1122]

创建一个存储过程，输入列名和表名，就可以判断该表上的改列有没有索引。
create proc testproc
@colname varchar(30),
@tablename varchar(30)
as
if exists(select * from sysindexkeys
where colid=(select id from syscolumns
where id=(select object_id(@tablename)) and name=@colname))
select @colname +'上没有索引'
else
select @colname +'上有索引'

[duanduan1122]

更正：
创建一个存储过程，输入列名和表名，就可以判断该表上的改列有没有索引。
create proc testproc
@colname varchar(30),
@tablename varchar(30)
as
if exists(select * from sysindexkeys
where colid=(select colid from syscolumns
where id=(select object_id(@tablename)) and name=@colname))
select @colname +'上有索引'
else
select @colname +'上没有索引'

[frogshero]

select * from syscolumns where id =object_id('a') and colid in (select colid from sysindexkeys where id=object_id('a'))
----这个会把在a,b字段上的索引也算上了,我要只有a字段的索引

duanduan1122也不大对吧

请再看看

[talantlee]

select * from syscolumns where id =object_id('a') and colid in (select colid from sysindexkeys where id=object_id('a')) and name='a'---字段名

[talantlee]

下面的可以實現了
alter proc test_checkindex
@vtbname varchar(100), --表名
@vcolumnname varchar(100)---字段名

as
declare @vname nvarchar(100)
select @vname =name from sysindexes
where id=object_id(@vtbname) and indid in( select indid from sysindexkeys where id=object_id(@vtbname)
and colid in (select colid from syscolumns where name=@vcolumnname and id=object_id('a')))
if @@rowcount=0
print '該表該列沒有索引'
else
print '該列的索引名 為 '''+@vname+''''