-- 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).


CREATE TABLE #CMCHECKCONSISTENCY (INCONSISTENCY INT not null, DESCRIPTION varchar(255), CMID int)
GO

DROP PROC getInconsistentCMIDs
GO

-- CMSCRIPT_SKIP_COMMAND
DROP PROC printInconsistentCMIDs
GO

CREATE PROCEDURE getInconsistentCMIDs AS
DECLARE @tablename char(20)
DECLARE @CMID int
DECLARE @columnname varchar(30)
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 = 'insert into #CMCHECKCONSISTENCY (INCONSISTENCY,DESCRIPTION , CMID) select 1,''' + @tablename + ''', CMID 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 #CMCHECKCONSISTENCY (INCONSISTENCY,DESCRIPTION , CMID) select 2, ''CMOBJECTS'', CMID from CMOBJECTS where CMID not in (select CMID from CMOBJNAMES) or CMID not in (select CMID from CMPOLICIES)'
EXEC sp_executesql @strsql
SET @strsql = 'insert into #CMCHECKCONSISTENCY (INCONSISTENCY,DESCRIPTION , CMID) select 3, ''CMSTOREIDS'', CMID from CMSTOREIDS where STOREID is null'
EXEC sp_executesql @strsql
SET @strsql = 'insert into #CMCHECKCONSISTENCY (INCONSISTENCY,DESCRIPTION , CMID) select 4, ''CMSTOREIDS'', s.CMID from CMSTOREIDS s where UPPER(s.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 NOCOUNT OFF
GO

-- CMSCRIPT_SKIP_COMMAND
CREATE PROCEDURE printInconsistentCMIDs AS
DECLARE @tablename char(20)
DECLARE @CMID int
DECLARE @columnname varchar(30)
DECLARE @strsql Nvarchar(500)
SET NOCOUNT ON
DECLARE summary CURSOR FOR
    SELECT DESCRIPTION, CMID FROM #CMCHECKCONSISTENCY WHERE INCONSISTENCY = 1 ORDER BY DESCRIPTION, CMID ASC
OPEN summary
FETCH NEXT FROM summary INTO @tablename, @CMID
IF @@fetch_status = 0
   BEGIN
      PRINT 'CMIDs not in CMOBJECTS table'
      PRINT 'Table               Orphaned CMID'
      PRINT '-----               -------------'
   END
WHILE @@fetch_status = 0
    BEGIN
        PRINT @tablename + RTRIM(CONVERT(varchar(10), @CMID))
        FETCH NEXT FROM summary INTO @tablename, @CMID
    END
CLOSE summary
DEALLOCATE summary
DECLARE summary CURSOR FOR
    SELECT DESCRIPTION, CMID FROM #CMCHECKCONSISTENCY WHERE INCONSISTENCY = 2 ORDER BY DESCRIPTION, CMID ASC
OPEN summary
FETCH NEXT FROM summary INTO @tablename, @CMID
IF @@fetch_status = 0
   BEGIN
      PRINT ''
      PRINT 'CMIDs in CMOBJECTS table but not in CMOBJNAMES or CMPOLICIES tables'
      PRINT 'Table               CMID'
      PRINT '-----               -------------'
   END
WHILE @@fetch_status = 0
    BEGIN
        PRINT @tablename + RTRIM(CONVERT(varchar(10), @CMID))
        FETCH NEXT FROM summary INTO @tablename, @CMID
    END
CLOSE summary
DEALLOCATE summary
DECLARE summary CURSOR FOR
    SELECT DESCRIPTION, CMID FROM #CMCHECKCONSISTENCY WHERE INCONSISTENCY = 4 ORDER BY DESCRIPTION, CMID ASC
OPEN summary
FETCH NEXT FROM summary INTO @tablename, @CMID
IF @@fetch_status = 0
   BEGIN
      PRINT ''
      PRINT 'CMIDs in CMSTOREIDS table which have non-unique STOREIDs'
      PRINT 'Table               CMID'
      PRINT '-----               -------------'
   END
WHILE @@fetch_status = 0
    BEGIN
        PRINT @tablename + RTRIM(CONVERT(varchar(10), @CMID))
        FETCH NEXT FROM summary INTO @tablename, @CMID
    END
CLOSE summary
DEALLOCATE summary
DECLARE summary CURSOR FOR
    SELECT DESCRIPTION, CMID FROM #CMCHECKCONSISTENCY WHERE INCONSISTENCY = 3 ORDER BY DESCRIPTION, CMID ASC
OPEN summary
FETCH NEXT FROM summary INTO @tablename, @CMID
IF @@fetch_status = 0
   BEGIN
      PRINT ''
      PRINT 'CMIDs in CMSTOREIDS table where STOREID is null'
      PRINT 'Table               CMID'
      PRINT '-----               -------------'
   END
WHILE @@fetch_status = 0
    BEGIN
        PRINT @tablename + RTRIM(CONVERT(varchar(10), @CMID))
        FETCH NEXT FROM summary INTO @tablename, @CMID
    END
CLOSE summary
DEALLOCATE summary
SET NOCOUNT OFF
GO

EXEC getInconsistentCMIDs
GO

-- CMSCRIPT_SKIP_COMMAND
EXEC printInconsistentCMIDs
GO

DROP PROC getInconsistentCMIDs
GO

-- CMSCRIPT_SKIP_COMMAND
DROP PROC printInconsistentCMIDs
GO

-- CMSCRIPT_SKIP_COMMAND
DROP TABLE #CMCHECKCONSISTENCY
GO