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