–create function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IndexExistsOnSchema]
(
@SCHEMA VARCHAR(50),
@INDEX VARCHAR(255)
)
RETURNS INT
AS
BEGIN
DECLARE @CNT INT
SET @CNT = 0
SELECT @CNT = COUNT(*)
FROM SYS.INDEXES
WHERE OBJECT_ID = OBJECT_ID(@SCHEMA)
AND name = @INDEX
RETURN @CNT
END
GO
–use function:
IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME, @INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME + ‘ DISABLE’);
–or
–or
IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME,@INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME+ ‘ REBUILD’);