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 | ||
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
Version Information
- Entered : 06/02/2016
Related SQLs
You could leave a comment if you were logged in.