This is an old revision of the document!


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
TableNameThe name of the table.
NotesList the reason the table needs correcting. Also, if an error occurs while repairing the primary key, the error text will be included.
HasErrorBIT 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

Version Information

  • Entered : 06/02/2016

Related SQLs


You could leave a comment if you were logged in.