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

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

DECLARE @ApplyChanges BIT;

SET @ApplyChanges 0;

DECLARE @TableName VARCHAR(255);

DECLARE @Col_ID INT;;

DECLARE @Col_StoreID INT;

DECLARE @Col_ClassTypeID INT;

DECLARE @Col_SeqID INT;

DECLARE @PKFields VARCHAR(MAX);

DECLARE @PrimaryKeyIndexName VARCHAR(255);

DECLARE @IsPrimaryKeyClustered BIT;

DECLARE @ClusteredIndexName VARCHAR(255);

DECLARE @HasValidPrimaryKey BIT;

DECLARE @HasDuplicateIDs BIT;

DECLARE @HasDuplicateIDClassTypes BIT;

DECLARE @Results TABLE(TableName VARCHAR(255), Notes VARCHAR(MAX), HasError BIT);

DECLARE @Notes VARCHAR(MAX);

DECLARE @HasError BIT;

DECLARE @SQLQuery NVARCHAR(MAX)

DECLARE CUR CURSOR FOR

SELECT NAME
FROM   SYS.TABLES
WHERE  TYPE  'U'
       AND SCHEMA_ID  1

OPEN CUR

FETCH NEXT FROM CUR INTO @TableName

WHILE @@FETCH_STATUS 0

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_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)
BEGIN
  SET @PKFields  NULL;
  SET @PrimaryKeyIndexName  NULL;
  SET @IsPrimaryKeyClustered  NULL;
  SET @ClusteredIndexName  NULL;
  SET @HasValidPrimaryKey  NULL;
  SET @HasDuplicateIDs  NULL;
  SET @HasDuplicateIDClassTypes  NULL;
  SELECT @PrimaryKeyIndexName  i.name, @IsPrimaryKeyClustered  CASE WHEN i.TYPE  1 THEN 1 ELSE 0 END
  FROM   sys.indexes AS i
  WHERE i.is_primary_key  1 AND i.OBJECT_ID  OBJECT_ID( @TableName, 'U' )
  SELECT @ClusteredIndexName  i.name
  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)
  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)
  BEGIN
    IF (@IsPrimaryKeyClustered  0)
      SET @HasValidPrimaryKey  0;
    ELSE IF (@PKFields  'ID' OR @PKFields  'ID,ClassTypeID')
      SET @HasValidPrimaryKey  1;
    ELSE
      SET @HasValidPrimaryKey  0;
  END
  ELSE
    SET @HasValidPrimaryKey  0;
  IF (@HasValidPrimaryKey  0)
  BEGIN
    SET @HasError  0;
    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
    SET @SQLQuery  N'SELECT
                        @Result  CASE
                                    WHEN EXISTS( SELECT   ID, ClassTypeID
                                                 FROM     [' + @TableName + ']
                                                 GROUP BY ID, ClassTypeID
                                                 HAVING   COUNT(0) > 1 ) THEN 1
                                  ELSE 0
                                  END';
    EXEC sp_executesql @SQLQuery, N'@Result BIT OUT', @HasDuplicateIDClassTypes out;
    IF (@HasDuplicateIDClassTypes  1)
    BEGIN
      SET @HasError  1;
      SET @Notes  @Notes + '; Error creating primary key:  Duplicate ID,ClassTypeID combinations detected';
    END
    ELSE IF (@ApplyChanges  1)
    BEGIN TRY
      SET @SQLQuery  N'SELECT
                          @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

CLOSE CUR

DEALLOCATE CUR

SELECT *

FROM @Results

ORDER BY TableName

<code> Version Information

  • Entered : 06/02/2016

Related SQLs

You could leave a comment if you were logged in.