SQL Compare does not detect disabled indexes

shawnCshawnC Posts: 17
Hi


tl;dr: SQL Compare does not detect disabled indexes

More...

I was playing with a bulk load script for test data.
In this, I disable constraints (CHECK and FK) and triggers.

However I wanted to disable some indexes temporarily. It failed and I needed to reset the database schema.

I thought I re-enabled all indexes (and constraints and triggers) and this appeared to be all OK when I used SQL Compare.
However, my overnight maintenance jobs failed because an index on an indexed view was still disabled. I had used sp_MSForEachTable which skipped the view

Repro script:
/*
Run on 2 databasee
*/
DROP VIEW dbo.FooView; DROP TABLE dbo.Foo; 
GO
CREATE TABLE dbo.Foo (FooID int NOT NULL PRIMARY KEY, Other char(20) NOT NULL CHECK (Other <> 'Seven'));
GO
INSERT dbo.Foo VALUES (1, 'one'),(2, 'two'),(3, 'three'),(4, 'four'),(5, 'five'),(6, 'six');
GO
CREATE NONCLUSTERED INDEX IX_Other ON dbo.Foo (Other);
GO
CREATE TRIGGER TRG_Foo ON dbo.Foo FOR DELETE AS SET NOCOUNT ON;
GO
CREATE VIEW dbo.FooView
WITH SCHEMABINDING
AS
SELECT F.FooID, F.Other FROM dbo.Foo F WHERE FooID >= 4;
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_PK ON dbo.FooView (FooID);
GO
SELECT * FROM dbo.Foo;SELECT * FROM dbo.FooView;
GO


/*
A SQL Compare now shows PK and UQ have different system generated name

Now disable stuff in one database only
*/


GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO
ALTER INDEX ALL ON dbo.Foo DISABLE;
ALTER TABLE dbo.Foo NOCHECK CONSTRAINT ALL; 
ALTER TABLE dbo.Foo DISABLE TRIGGER ALL;
GO
ALTER INDEX IXCU_PK ON dbo.FooView DISABLE;
GO
SELECT * FROM sys.indexes WHERE sys.indexes.is_disabled = 1;SELECT * FROM sys.check_constraints CC WHERE CC.is_not_trusted = 1;SELECT * FROM sys.triggers T WHERE T.is_disabled = 1;
GO

/*
A SQL Compare now does not show differences in the indexes (expected: 2 on dbo.Foo, 1 on dbo.FooView).. but it does show the CHECK constraint differences
*/

DROP VIEW dbo.FooView; DROP TABLE dbo.Foo; 
GO

Comments

Sign In or Register to comment.