我删除一个字段,
ALTER TABLE Bill_LinkSet DROP COLUMN Importdetailid
它会提示存在相关的约束或者索引
然后我要删除这个索引。才能删除这个字段
要分两步,我想用一步做完,就是 怎么查看这个字段的索引,
如果能查出来,两步写在一个脚本里,直接做完
ALTER TABLE Bill_LinkSet DROP COLUMN Importdetailid
它会提示存在相关的约束或者索引
然后我要删除这个索引。才能删除这个字段
要分两步,我想用一步做完,就是 怎么查看这个字段的索引,
如果能查出来,两步写在一个脚本里,直接做完
如果没有,就直接drop column
如果有就,drop index
ELSE
BEGIN
CREATE
INDEX [Pm_ProjectWbsRelation_Index] ON [dbo].[Pm_ProjectWbsRelation] ([Ppw_Id], [Pwr_Type], [Ppw_AllParentId])
ON [PRIMARY]
END
GO
IF EXISTS (SELECT 1 FROM Sysindexes WHERE NAME = '索引名字')
删除索引--第二步
删除字段
exec sp_helpindex tb--结合sys.indexes和sys.index_columns,sys.objects,sys.columns查询索引所属的表或视图的信息
select
o.name as 表名,
i.name as 索引名,
c.name as 列名,
i.type_desc as 类型描述,
is_primary_key as 主键约束,
is_unique_constraint as 唯一约束,
is_disabled as 禁用
from
sys.objects o
inner join
sys.indexes i
on
i.object_id=o.object_id
inner join
sys.index_columns ic
on
ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
sys.columns c
on
ic.column_id=c.column_id and ic.object_id=c.object_id
go--查询索引的键和列信息
select
o.name as 表名,
i.name as 索引名,
c.name as 字段编号,
from
sysindexes i inner join sysobjects o
on
i.id=o.id
inner join
sysindexkeys k
on
o.id=k.id and i.indid=k.indid
inner join
syscolumns c
on
c.id=i.id and k.colid=c.colid
where
o.name='表名'
select * from sys.indexes where object_id= object_id('test') -- 2008的