-- Name:
-- sp_MSmerge_dropindex
-- Description:
-- This procedure drops the index on the table that referes to the passed in column.
-- Parameters:
-- See the procedure definition.
-- Returns:
-- 0 - On success
-- 1 - On Failure
-- Result:
-- None
-- Security:
-- None as this procedure is not public.
CREATE PROCEDURE sys.sp_MSmerge_dropindex
@table_name sysname,
@index_column_name sysname
AS
BEGIN
declare @index_name sysname
declare @retcode int
declare @cmd nvarchar(1000)
set @retcode = 0
declare #indexes cursor for
select inds.name
from sys.columns cols JOIN sys.index_columns icols ON cols.object_id = icols.object_id and cols.column_id = icols.column_id
JOIN sys.indexes inds ON inds.object_id = icols.object_id and inds.index_id = icols.index_id
where cols.object_id = OBJECT_ID(@table_name) and cols.name = @index_column_name
open #indexes
fetch next from #indexes into @index_name
while @@fetch_status <> -1
begin
select @cmd = 'drop index ' + quotename(@table_name) + '.' + quotename(@index_name)
exec (@cmd)
if @@ERROR <> 0
begin
set @retcode = 1
goto UNDO
end
fetch next from #indexes into @index_name
end
UNDO:
close #indexes
deallocate #indexes
return @retcode
END