-- Licensed Materials - Property of IBM
--
-- BI and PM: CM
--
-- (C) Copyright IBM Corp. 2008, 2009
--
-- US Government Users Restricted Rights - Use, duplication or disclosure
-- restricted by GSA ADP Schedule Contract with IBM Corp.

-- Copyright (C) 2008 Cognos ULC, an IBM Company. All rights reserved.
-- Cognos (R) is a trademark of Cognos ULC, (formerly Cognos Incorporated).


IF OBJECT_ID('makeCSConsistent') IS NOT NULL
 DROP PROC makeCSConsistent
GO

CREATE PROCEDURE makeCSConsistent AS
DECLARE @tablename char(20)
DECLARE @strsql Nvarchar(500)
SET NOCOUNT ON
DECLARE tables CURSOR FOR
    SELECT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'CMID' and TABLE_NAME like 'CM%' and TABLE_SCHEMA = user_name()
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@fetch_status = 0
    BEGIN
        SET @strsql = 'delete from ' + @tablename + ' where CMID not in (select CMID from CMOBJECTS)'
        EXEC sp_executesql @strsql        
        FETCH NEXT FROM tables INTO @tablename
    END
CLOSE tables
DEALLOCATE tables
SET @strsql = 'insert into CMOBJNAMES (CMID, LOCALEID, MAPDLOCALEID, ISDEFAULT, NAME) select CMID, 24, 24, 1, ''REPAIRED BY CONSISTENCY CHECK '' + CONVERT(nvarchar(255), newid()) from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES)'
EXEC sp_executesql @strsql
SET @strsql = 'insert into CMPOLICIES (CMID) select CMID from CMOBJECTS where CMID not in (select CMID from CMPOLICIES)'
EXEC sp_executesql @strsql
SET @strsql = 'insert into CMSTOREIDS (CMID, STOREID) select CMID, substring(''i00000000000000000000000000000000'',1,33 - len(CONVERT(nvarchar(10), CMID))) + CONVERT(nvarchar(10), CMID) from CMOBJECTS where CMID not in (select CMID from CMSTOREIDS)'
EXEC sp_executesql @strsql
SET @strsql = 'update CMSTOREIDS set STOREID = NULL where UPPER(STOREID) in (select UPPERSTOREIDS.STOREID from (select b.CMID, UPPER(b.STOREID) as STOREID from CMSTOREIDS b) as UPPERSTOREIDS group by UPPERSTOREIDS.STOREID having COUNT(UPPERSTOREIDS.CMID) > 1)'
EXEC sp_executesql @strsql
SET @strsql = 'update CMSTOREIDS set STOREID = substring(''i00000000000000000000000000000000'',1,33 - len(CONVERT(nvarchar(10), CMID))) + CONVERT(nvarchar(10), CMID) where CMID in (select CMID from CMSTOREIDS where STOREID is null)'
EXEC sp_executesql @strsql

SET NOCOUNT OFF
GO

EXEC makeCSConsistent

DROP PROC makeCSConsistent