Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Last revision Both sides next revision
check_and_repair_primary_keys [2019/01/30 09:22]
127.0.0.1 external edit 		check_and_repair_primary_keys [2019/02/20 11:03]
kcifreo
Line 1: Line 1:
- +Explanation of SQL
  
 +This query will check all the tables in a database to ensure that the primary keys exist and are valid for Control table.
  
- +  * If an invalid primary key and/or clustered index is found on a table, it will be deleted.
-Explanation of SQL  +
- +
- +
- +
-This query will check all the tables in a database to ensure that the primary keys exist and are valid for Control table. +
-  * If an invalid primary key and/or clustered index is found on a table, it will be deleted.  +
   * If no valid primary key is found, or an invalid primary is found and deleted, a valid primary key will be created.   * If no valid primary key is found, or an invalid primary is found and deleted, a valid primary key will be created.
   * A valid primary key is a clustered index on the ID field (or ID and ClassTypeID is just the ID is not possible because of duplicate ID numbers).   * A valid primary key is a clustered index on the ID field (or ID and ClassTypeID is just the ID is not possible because of duplicate ID numbers).
   * This query is automatically run on startup of Chapi starting with version ?.?.? (the version included in Control 5.75).   * This query is automatically run on startup of Chapi starting with version ?.?.? (the version included in Control 5.75).
- 
- 
  
 This query returns a table with 3 columns listing the tables with missing or invalid primary keys. This query returns a table with 3 columns listing the tables with missing or invalid primary keys.
  
 +|~ Column Name||~ Description||
 +|TableName||The name of the table.||
 +|Notes||List the reason the table needs correcting. Also, if an error occurs while repairing the primary key, the error text will be included.||
 +|HasError||BIT field to indicate if an error has occurred while repairing the primary key. One indicates there was an error. Zero indicates there was no errors.||
  
- +Risk of Data Corruption if Run Improperly
-||~ Column Name ||~ Description || +
-|| TableName || The name of the table. || +
-|| Notes || List the reason the table needs correcting.  Also, if an error occurs while repairing the primary key, the error text will be included. || +
-|| HasError || BIT field to indicate if an error has occurred while repairing the primary key.  One indicates there was an error.  Zero indicates there was no errors. || +
-Risk of Data Corruption if Run Improperly +
  
 **High**. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and non-reversable**. **High**. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and non-reversable**.
  
 +SQL
  
 +<code>
 +code format"sql"
  
-SQL  +– Setting @ApplyChanges to 0 (zero) will just return a report of the tables with invalid or missing primary keys.
- +
- +
- +
-[[code_formatsql|code format"sql"]] +
- +
- +
- +
--- Setting @ApplyChanges to 0 (zero) will just return a report of the tables with invalid or missing primary keys. +
- +
- +
- +
--- Setting @ApplyChanges to 1 (one) will also alter the tables to correct the primary keys. +
  
 +– Setting @ApplyChanges to 1 (one) will also alter the tables to correct the primary keys.
  
 DECLARE @ApplyChanges BIT; DECLARE @ApplyChanges BIT;
  
- +SET @ApplyChanges 0;
- +
-SET @ApplyChanges  0; +
- +
  
 DECLARE @TableName VARCHAR(255); DECLARE @TableName VARCHAR(255);
- 
- 
  
 DECLARE @Col_ID INT;; DECLARE @Col_ID INT;;
- 
- 
  
 DECLARE @Col_StoreID INT; DECLARE @Col_StoreID INT;
- 
- 
  
 DECLARE @Col_ClassTypeID INT; DECLARE @Col_ClassTypeID INT;
- 
- 
  
 DECLARE @Col_SeqID INT; DECLARE @Col_SeqID INT;
- 
- 
  
 DECLARE @PKFields VARCHAR(MAX); DECLARE @PKFields VARCHAR(MAX);
- 
- 
  
 DECLARE @PrimaryKeyIndexName VARCHAR(255); DECLARE @PrimaryKeyIndexName VARCHAR(255);
- 
- 
  
 DECLARE @IsPrimaryKeyClustered BIT; DECLARE @IsPrimaryKeyClustered BIT;
- 
- 
  
 DECLARE @ClusteredIndexName VARCHAR(255); DECLARE @ClusteredIndexName VARCHAR(255);
- 
- 
  
 DECLARE @HasValidPrimaryKey BIT; DECLARE @HasValidPrimaryKey BIT;
- 
- 
  
 DECLARE @HasDuplicateIDs BIT; DECLARE @HasDuplicateIDs BIT;
- 
- 
  
 DECLARE @HasDuplicateIDClassTypes BIT; DECLARE @HasDuplicateIDClassTypes BIT;
- 
- 
  
 DECLARE @Results TABLE(TableName VARCHAR(255), Notes VARCHAR(MAX), HasError BIT); DECLARE @Results TABLE(TableName VARCHAR(255), Notes VARCHAR(MAX), HasError BIT);
- 
- 
  
 DECLARE @Notes VARCHAR(MAX); DECLARE @Notes VARCHAR(MAX);
- 
- 
  
 DECLARE @HasError BIT; DECLARE @HasError BIT;
- 
- 
  
 DECLARE @SQLQuery NVARCHAR(MAX) DECLARE @SQLQuery NVARCHAR(MAX)
- 
- 
  
 DECLARE CUR CURSOR FOR DECLARE CUR CURSOR FOR
  
 +SELECT NAME
  
 +FROM   SYS.TABLES
  
-  SELECT NAME +WHERE  TYPE  'U'
- +
- +
- +
-  FROM   SYS.TABLES +
- +
- +
- +
-  WHERE  TYPE  'U' +
- +
- +
- +
-         AND SCHEMA_ID  1 +
  
 +       AND SCHEMA_ID  1
  
 OPEN CUR OPEN CUR
- 
- 
  
 FETCH NEXT FROM CUR INTO @TableName FETCH NEXT FROM CUR INTO @TableName
  
- +WHILE @@FETCH_STATUS 0
- +
-WHILE @@FETCH_STATUS  0 +
- +
  
 BEGIN BEGIN
  
 +SET @Col_ID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'ID');
  
 +SET @Col_StoreID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'StoreID');
  
-  SET @Col_ID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'ID');+SET @Col_ClassTypeID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'ClassTypeID');
  
 +SET @Col_SeqID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'SeqID');
 +-- Check if it's a standard Control table
  
 +IF (@Col_ID  1 AND @Col_StoreID  2 AND @Col_ClassTypeID  3 AND @Col_SeqID IS NOT NULL)
  
-  SET @Col_StoreID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'StoreID');+BEGIN
  
 +  SET @PKFields  NULL;
  
 +  SET @PrimaryKeyIndexName  NULL;
  
-  SET @Col_ClassTypeID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'ClassTypeID');+  SET @IsPrimaryKeyClustered  NULL;
  
 +  SET @ClusteredIndexName  NULL;
  
 +  SET @HasValidPrimaryKey  NULL;
  
-  SET @Col_SeqID  (SELECT column_id FROM sys.COLUMNS c WHERE c.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) AND name  'SeqID'); +  SET @HasDuplicateIDs  NULL;
-  -- Check if it's a standard Control table+
  
 +  SET @HasDuplicateIDClassTypes  NULL;
  
 +  SELECT @PrimaryKeyIndexName  i.name, @IsPrimaryKeyClustered  CASE WHEN i.TYPE  1 THEN 1 ELSE 0 END
  
-  IF (@Col_ID  1 AND @Col_StoreID  2 AND @Col_ClassTypeID  3 AND @Col_SeqID IS NOT NULL)+  FROM   sys.indexes AS i
  
 +  WHERE i.is_primary_key  1 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U' )
  
 +  SELECT @ClusteredIndexName  i.name
  
-  BEGIN+  FROM   sys.indexes AS i
  
 +  WHERE i.TYPE  1 AND i.is_primary_key  0 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U' )
  
 +  SELECT @PKFields  COALESCE(@PKFields+',' ,'') + COL_NAME(ic.OBJECT_ID,ic.column_id)
  
-    SET @PKFields  NULL;+  FROM    sys.indexes AS i INNER JOIN
  
 +          sys.index_columns AS ic ON  i.OBJECT_ID  ic.OBJECT_ID
  
 +                                  AND i.index_id  ic.index_id
  
-    SET @PrimaryKeyIndexName  NULL;+  WHERE   i.is_primary_key  1 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U')
  
 +  ORDER BY ic.index_column_id
  
 +  IF (@PrimaryKeyIndexName IS NOT NULL)
  
-    SET @IsPrimaryKeyClustered  NULL;+  BEGIN
  
 +    IF (@IsPrimaryKeyClustered  0)
  
 +      SET @HasValidPrimaryKey  0;
  
-    SET @ClusteredIndexName  NULL;+    ELSE IF (@PKFields  'ID' OR @PKFields  'ID,ClassTypeID')
  
 +      SET @HasValidPrimaryKey  1;
  
 +    ELSE
  
-    SET @HasValidPrimaryKey  NULL;+      SET @HasValidPrimaryKey  0;
  
 +  END
  
 +  ELSE
  
-    SET @HasDuplicateIDs  NULL;+    SET @HasValidPrimaryKey  0;
  
 +  IF (@HasValidPrimaryKey  0)
  
 +  BEGIN
  
-    SET @HasDuplicateIDClassTypes  NULL;+    SET @HasError  0;
  
 +    IF (@PrimaryKeyIndexName IS NULL)
  
 +      SET @Notes  'No Primary Key';
  
-    SELECT @PrimaryKeyIndexName  i.name, @IsPrimaryKeyClustered  CASE WHEN i.TYPE  1 THEN 1 ELSE 0 END+    ELSE
  
 +    BEGIN
  
 +      SET @Notes  'Invalid Primary Key (' + @PrimaryKeyIndexName + ' - ';
  
-    FROM   sys.indexes AS i+      IF (@IsPrimaryKeyClustered  1)
  
 +        SET @Notes  @Notes + 'Clustered)';
  
 +      ELSE
  
-    WHERE i.is_primary_key  1 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U' )+        SET @Notes  @Notes + 'Not Clustered)';
  
 +      IF (@ApplyChanges  1)
  
 +      BEGIN TRY
  
-    SELECT @ClusteredIndexName  i.name+        EXECUTE( 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ' + @PrimaryKeyIndexName );
  
 +      END TRY
  
 +      BEGIN CATCH
  
-    FROM   sys.indexes AS i+        SET @HasError  1;
  
 +        SET @Notes  @Notes + '; Error dropping primary key:  ' + ERROR_MESSAGE();
  
 +      END CATCH
  
-    WHERE i.TYPE  1 AND i.is_primary_key  0 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U' ) +    END;
- +
- +
- +
-    SELECT @PKFields  COALESCE(@PKFields+',' ,'') + COL_NAME(ic.OBJECT_ID,ic.column_id) +
- +
- +
- +
-    FROM    sys.indexes AS i INNER JOIN +
- +
- +
- +
-            sys.index_columns AS ic ON  i.OBJECT_ID  ic.OBJECT_ID +
- +
- +
- +
-                                    AND i.index_id  ic.index_id +
- +
- +
- +
-    WHERE   i.is_primary_key  1 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U') +
- +
- +
- +
-    ORDER BY ic.index_column_id +
- +
- +
- +
-    IF (@PrimaryKeyIndexName IS NOT NULL) +
  
 +    IF (@ClusteredIndexName IS NOT NULL)
  
     BEGIN     BEGIN
  
 +      SET @Notes  @Notes + '; Had Clustered Index (' + @ClusteredIndexName + ')';
  
 +      IF (@ApplyChanges  1)
  
-      IF (@IsPrimaryKeyClustered  0)+      BEGIN TRY
  
 +        EXECUTE( 'DROP INDEX ' + @ClusteredIndexName + ' ON [' + @TableName + ']' );
  
 +      END TRY
  
-        SET @HasValidPrimaryKey  0;+      BEGIN CATCH
  
 +        SET @HasError  1;
  
 +        SET @Notes  @Notes + '; Error dropping clustered index:  ' + ERROR_MESSAGE();
  
-      ELSE IF (@PKFields  'ID' OR @PKFields  'ID,ClassTypeID') +      END CATCH
- +
- +
- +
-        SET @HasValidPrimaryKey  1; +
- +
- +
- +
-      ELSE +
- +
- +
- +
-        SET @HasValidPrimaryKey  0; +
- +
  
     END     END
  
 +    SET @SQLQuery  N'SELECT
  
 +                        @Result  CASE
  
-    ELSE+                                    WHEN EXISTS( SELECT   ID, ClassTypeID
  
 +                                                 FROM     [' + @TableName + ']
  
 +                                                 GROUP BY ID, ClassTypeID
  
-      SET @HasValidPrimaryKey  0; +                                                 HAVING   COUNT(0)> 1 ) THEN 1
  
 +                                  ELSE 0
  
-    IF (@HasValidPrimaryKey  0)+                                  END';
  
 +    EXEC sp_executesql @SQLQuery, N'@Result BIT OUT', @HasDuplicateIDClassTypes out;
  
 +    IF (@HasDuplicateIDClassTypes  1)
  
     BEGIN     BEGIN
  
 +      SET @HasError  1;
  
 +      SET @Notes  @Notes + '; Error creating primary key:  Duplicate ID,ClassTypeID combinations detected';
  
-      SET @HasError  0; +    END
- +
- +
- +
-      IF (@PrimaryKeyIndexName IS NULL) +
- +
- +
- +
-        SET @Notes  'No Primary Key'; +
- +
- +
- +
-      ELSE +
- +
- +
- +
-      BEGIN +
- +
- +
- +
-        SET @Notes  'Invalid Primary Key (' + @PrimaryKeyIndexName + ' - '; +
- +
- +
- +
-        IF (@IsPrimaryKeyClustered  1) +
- +
- +
- +
-          SET @Notes  @Notes + 'Clustered)'; +
- +
- +
- +
-        ELSE +
- +
- +
- +
-          SET @Notes  @Notes + 'Not Clustered)'; +
- +
- +
- +
-        IF (@ApplyChanges  1) +
- +
- +
- +
-        BEGIN TRY +
- +
- +
- +
-          EXECUTE( 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ' + @PrimaryKeyIndexName ); +
- +
- +
- +
-        END TRY +
- +
- +
- +
-        BEGIN CATCH +
- +
- +
- +
-          SET @HasError  1; +
- +
- +
- +
-          SET @Notes  @Notes + '; Error dropping primary key:  ' + ERROR_MESSAGE(); +
- +
- +
- +
-        END CATCH +
- +
- +
- +
-      END; +
- +
- +
- +
-      IF (@ClusteredIndexName IS NOT NULL) +
- +
- +
- +
-      BEGIN +
- +
- +
- +
-        SET @Notes  @Notes + '; Had Clustered Index (' + @ClusteredIndexName + ')'; +
- +
- +
- +
-        IF (@ApplyChanges  1) +
- +
- +
- +
-        BEGIN TRY +
- +
- +
- +
-          EXECUTE( 'DROP INDEX ' + @ClusteredIndexName + ' ON [' + @TableName + ']' ); +
- +
- +
- +
-        END TRY +
- +
- +
- +
-        BEGIN CATCH +
- +
- +
- +
-          SET @HasError  1; +
- +
- +
- +
-          SET @Notes  @Notes + '; Error dropping clustered index:  ' + ERROR_MESSAGE(); +
- +
- +
- +
-        END CATCH +
- +
- +
- +
-      END+
  
 +    ELSE IF (@ApplyChanges  1)
  
 +    BEGIN TRY
  
       SET @SQLQuery  N'SELECT       SET @SQLQuery  N'SELECT
- 
- 
  
                           @Result  CASE                           @Result  CASE
  
- +                                      WHEN EXISTS( SELECT   ID
- +
-                                      WHEN EXISTS( SELECT   ID, ClassTypeID +
- +
  
                                                    FROM     [' + @TableName + ']                                                    FROM     [' + @TableName + ']
  
 +                                                   GROUP BY ID
  
- +                                                   HAVING   COUNT(0)> 1 ) THEN 1
-                                                   GROUP BY ID, ClassTypeID +
- +
- +
- +
-                                                   HAVING   COUNT(0) > 1 ) THEN 1 +
- +
  
                                     ELSE 0                                     ELSE 0
- 
- 
  
                                     END';                                     END';
  
 +      EXEC sp_executesql @SQLQuery, N'@Result BIT OUT', @HasDuplicateIDs out;
  
 +      SET @SQLQuery  'ALTER TABLE [' + @TableName + '] ADD CONSTRAINT PK_' + REPLACE(  @TableName, '-', '' ) + ' PRIMARY KEY CLUSTERED (ID';
  
-      EXEC sp_executesql @SQLQuery, N'@Result BIT OUT', @HasDuplicateIDClassTypes out; +      IF (@HasDuplicateIDs  1)
- +
- +
- +
-      IF (@HasDuplicateIDClassTypes  1) +
- +
  
       BEGIN       BEGIN
  
 +        SET @SQLQuery  @SQLQuery + ',ClassTypeID';
  
- +        SET @Notes  @Notes + '; Duplicate IDs detected.  New Primary Key includes ClassTypeID.';
-        SET @HasError  1; +
- +
- +
- +
-        SET @Notes  @Notes + '; Error creating primary key:  Duplicate ID,ClassTypeID combinations detected'; +
- +
  
       END       END
  
 +      SET @SQLQuery  @SQLQuery + ')';
  
 +      EXECUTE(@SQLQuery);
  
-      ELSE IF (@ApplyChanges  1)+    END TRY
  
 +    BEGIN CATCH
  
 +      SET @HasError  1;
  
-      BEGIN TRY+      SET @Notes  @Notes + '; Error creating primary key:  ' + ERROR_MESSAGE();
  
 +    END CATCH
  
 +    INSERT INTO @Results VALUES (@TableName, @Notes, @HasError);
  
-        SET @SQLQuery  N'SELECT +  END
- +
- +
- +
-                            @Result  CASE +
- +
- +
- +
-                                        WHEN EXISTS( SELECT   ID +
- +
- +
- +
-                                                     FROM     [' + @TableName + '] +
- +
- +
- +
-                                                     GROUP BY ID +
- +
- +
- +
-                                                     HAVING   COUNT(0) > 1 ) THEN 1 +
- +
- +
- +
-                                      ELSE 0 +
- +
- +
- +
-                                      END'; +
- +
- +
- +
-        EXEC sp_executesql @SQLQuery, N'@Result BIT OUT', @HasDuplicateIDs out; +
- +
- +
- +
-        SET @SQLQuery  'ALTER TABLE [' + @TableName + '] ADD CONSTRAINT PK_' + REPLACE(  @TableName, '-', '' ) + ' PRIMARY KEY CLUSTERED (ID'; +
- +
- +
- +
-        IF (@HasDuplicateIDs  1) +
- +
- +
- +
-        BEGIN +
- +
- +
- +
-          SET @SQLQuery  @SQLQuery + ',ClassTypeID'; +
- +
- +
- +
-          SET @Notes  @Notes + '; Duplicate IDs detected.  New Primary Key includes ClassTypeID.'; +
- +
- +
- +
-        END +
- +
- +
- +
-        SET @SQLQuery  @SQLQuery + ')'; +
- +
- +
- +
-        EXECUTE(@SQLQuery); +
- +
- +
- +
-      END TRY +
- +
- +
- +
-      BEGIN CATCH +
- +
- +
- +
-        SET @HasError  1; +
- +
- +
- +
-        SET @Notes  @Notes + '; Error creating primary key:  ' + ERROR_MESSAGE(); +
- +
- +
- +
-      END CATCH +
- +
- +
- +
-      INSERT INTO @Results VALUES (@TableName, @Notes, @HasError); +
- +
- +
- +
-    END +
- +
- +
- +
-  END; +
- +
- +
- +
-  FETCH NEXT FROM CUR INTO @TableName+
  
 +END;
  
 +FETCH NEXT FROM CUR INTO @TableName
  
 END END
- 
- 
  
 CLOSE CUR CLOSE CUR
- 
- 
  
 DEALLOCATE CUR DEALLOCATE CUR
- 
- 
  
 SELECT * SELECT *
  
 +FROM @Results
  
 +ORDER BY TableName
 +</code>
  
-FROM @Results+Version Information
  
 +  * Entered : 06/02/2016
  
 +Related SQLs
  
-ORDER BY TableName+''''
  
 +\\
  
- 
-<code> 
-Version Information  
-  * Entered : 06/02/2016 
-Related SQLs