MSSQL如何删除字段的所有约束和索引(二)
id, inds.name idxname from sys.index_columns cols
left join sys.indexes inds on cols.object_id=inds.object_id and cols.index_id=inds.index_id
where cols.object_id=@tid and column_id=@colid
return
end
go
--------------------------------------------------
--
-- mp_DropColumnIndexes
-- 功能：删除指定列的所有索引
-- 入口：
-- @TableName NVARCHAR(128) 表名
-- @ColumnName NVARCHAR(128) 列名
--------------------------------------------------
if OBJECT_ID(N'dbo.mp_DropColumnIndexes', N'P') is not null
drop procedure dbo.mp_DropColumnIndexes
go
create procedure dbo.mp_DropColumnIndexes
@TableName NVARCHAR(128),
@ColumnName NVARCHAR(128)
as www.2cto.com
begin
if OBJECT_ID(N'#t', N'TB') is not null
drop table #t
create table #t
(
id int,
name nvarchar(128)
)
insert into #t select * from mfn_GetColumnIndexes(@TableName, @ColumnName)
-- 删除索引
select @i=1, @imax=COALESCE(max(id), 0) from #t
while @i<=@imax
begin
select @idxname=name from #t
EXEC('drop index ' + @idxname + ' on ' + @tablename)
set @i=@i+1
end
drop table #t
end
go
------------------------------------------------
-- mp_DropColConstraintAndIndex
-- 功能：删除指定字段的所有约束和索引
-- 入口：
-- @TableName NVARCHAR(128) -- 表名
-- @ColumnName NVARCHAR(128) -- 列名
------------------------------------------------
if OBJECT_ID(N'dbo.mp_DropColConstraintAndIndex', N'P') is not null
drop procedure dbo.mp_DropColConstraintAndIndex
go www.2cto.com
create procedure dbo.mp_DropColConstraintAndIndex
@TableName NVARCHAR(128),
@ColumnName NVARCHAR(128)
as
begin
exec dbo.mp_DropColConstraint @TableName, @ColumnName
exec dbo.mp_DropColumnIndexes @TableName, @ColumnName
end
go
作者 niniu