ALTER PROCEDURE procSSWPrimaryKeyUpdateAllRelationships /* '---------------------------------------------- ' Copyright 2002 Superior Software for Windows '---------------------------------------------- ' www.ssw.com.au All Rights Reserved. ' VERSION AUTHOR DATE COMMENT ' 1.0 RD,ST 30/10/2002 ' SAMPLE USAGE: ' rename values in a column ' DECLARE @strMessage varchar(200) ' EXECUTE procSSWPrimaryKeyUpdateAllRelationships 'Client','ClientID', 'ZZTEST', 'ZZNEW1', @strReturnMessage = @strMessage OUTPUT '---------------------------------------------- (One table) Children (many table) UpdateCascadeON Client.ClientID |___________________ClientContact.ClientID Yes | |___________________ClientDiary.ClientID No | |___________________ClientInvoice.ClientID Yes Execution plan: - select children where CascadeUpdate is turned off (due to SQL Server limitations) - open a cursor - build SQL_NOCHECK statement to turn OFF Enforce Referential Integrity - build SQL_CHECK statement to turn ON Enforce Referential Integrity - build SQL_UPDATE statement to update children where UpdateCascadeON is NO (ClientDiary.ClientID) - close a cursor - append SQL_UPDATE with SQL updating main table (Client.ClientID) - execute SQL_NOCHECK - execute SQL_UPDATE - execute SQL_CHECK */ @pstrTableName varchar (200), @pstrColumnName varchar (200), @pstrOldValue varchar (200), @pstrNewValue varchar (200), @pstrEmpUpdated varchar (150), @strReturnMessage varchar (200) OUTPUT AS DECLARE @strForeignTableName varchar (200) DECLARE @strForeignColumnName varchar (200) DECLARE @strConstraintName varchar (200) DECLARE @strPrimaryTableName varchar (200) DECLARE @strPrimaryColumnName varchar (200) DECLARE @strColumnType varchar (10) -- select children where CascadeUpdate is turned off (due to SQL Server limitations) DECLARE curTableName_ConstraintName CURSOR FOR SELECT sysobjects_foreign.name AS foreign_table, syscolumns_foreign.name AS foreign_column, sysobjects_primary.name AS primary_table, syscolumns_primary.name AS primary_column, sysobjects.name AS constraint_name, systypes.name AS ColumnType FROM sysforeignkeys INNER JOIN sysobjects ON sysforeignkeys.constid = sysobjects.id INNER JOIN sysobjects sysobjects_primary ON sysforeignkeys.rkeyid = sysobjects_primary.id INNER JOIN sysobjects sysobjects_foreign ON sysforeignkeys.fkeyid = sysobjects_foreign.id AND sysobjects.parent_obj = sysobjects_foreign.id INNER JOIN syscolumns syscolumns_foreign ON sysforeignkeys.fkey = syscolumns_foreign.colid AND sysobjects_foreign.id = syscolumns_foreign.id INNER JOIN syscolumns syscolumns_primary ON sysforeignkeys.rkey = syscolumns_primary.colid AND sysobjects_primary.id = syscolumns_primary.id INNER JOIN systypes ON syscolumns_foreign.xusertype = systypes.xusertype WHERE OBJECTPROPERTY(sysobjects.id, 'CnstIsUpdateCascade') = 0 AND sysobjects_primary.name = @pstrTableName AND syscolumns_primary.name = @pstrColumnName ORDER BY sysobjects_foreign.name, syscolumns_foreign.name DECLARE @SQLStringNoCheckConstraint NVARCHAR(4000) SET @SQLStringNoCheckConstraint ='' DECLARE @SQLStringCheckConstraint NVARCHAR(4000) SET @SQLStringCheckConstraint = '' DECLARE @SQLStringUPDATE NVARCHAR(4000) SET @SQLStringUPDATE ='' DECLARE @strColumnTypetemp varchar (10) SET @strColumnTypeTemp = '' DECLARE @SQLStringSSWEmpUpdated NVARCHAR (500) SET @SQLStringSSWEmpUpdated = ',' + + CHAR(13) + CHAR(10) + 'EmpUpdated=' + '''' + @pstrEmpUpdated + '''' DECLARE @SQLStringSSWDateUpdated NVARCHAR (500) SET @SQLStringSSWDateUpdated = ',' + + CHAR(13) + CHAR(10) + 'DateUpdated=' + '''' + CONVERT(NVARCHAR,GETDATE()) + '''' -- open a cursor OPEN curTableName_ConstraintName FETCH NEXT FROM curTableName_ConstraintName INTO @strForeignTableName, @strForeignColumnName, @strPrimaryTableName, @strPrimaryColumnName, @strConstraintName, @strColumnType WHILE @@FETCH_STATUS = 0 BEGIN SET @strColumnTypeTemp = @strColumnType -- build SQL_NOCHECK statement to turn OFF Enforce Referential Integrity SET @SQLStringNoCheckConstraint = @SQLStringNoCheckConstraint + N'ALTER TABLE [' + @strForeignTableName + '] ' + 'NOCHECK CONSTRAINT [' + @strConstraintName + ']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) -- build SQL_CHECK statement to turn ON Enforce Referential Integrity SET @SQLStringCheckConstraint = @SQLStringCheckConstraint + N'ALTER TABLE [' + @strForeignTableName + '] ' + 'CHECK CONSTRAINT [' + @strConstraintName + ']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) -- build SQL_UPDATE statement to update children where UpdateCascadeON is NO (ClientDiary.ClientID) IF @strColumnType IN ('nchar', 'char', 'nvarchar', 'varchar') BEGIN SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @strForeignTableName + ']' + CHAR(13) + CHAR(10) + 'SET [' + @strForeignColumnName + ']=' + '''' + @pstrNewValue + '''' IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @strForeignColumnName + ']=' + ''''+ @pstrOldValue + '''' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) END ELSE BEGIN SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @strForeignTableName + ']' + CHAR(13) + CHAR(10) + 'SET [' + @strForeignColumnName + ']=' + @pstrNewValue IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @strForeignColumnName + ']=' + @pstrOldValue + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) END FETCH NEXT FROM curTableName_ConstraintName INTO @strForeignTableName, @strForeignColumnName, @strPrimaryTableName, @strPrimaryColumnName, @strConstraintName, @strColumnType END -- close a cursor CLOSE curTableName_ConstraintName DEALLOCATE curTableName_ConstraintName -- append SQL_UPDATE with SQL updating main table (Client.ClientID) IF @strColumnType IN ('nchar', 'char', 'nvarchar', 'varchar') BEGIN SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @pstrTableName + ']' + CHAR(13) + CHAR(10) + 'SET [' + @pstrColumnName + ']=' + ''''+ @pstrNewValue + '''' IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @pstrColumnName + ']=' + '''' + @pstrOldValue + '''' END ELSE BEGIN SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @pstrTableName + ']' + CHAR(13) + CHAR(10) + 'SET [' + @pstrColumnName + ']=' + @pstrNewValue IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated IF EXISTS (SELECT syscolumns.name FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated')) SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @pstrColumnName + ']=' + @pstrOldValue END -- execute SQL_NOCHECK EXECUTE sp_executesql @SQLStringNoCheckConstraint DECLARE @intCount int SET @intCount = 0 DECLARE @intError int SET @intError = 0 BEGIN TRANSACTION -- execute SQL_UPDATE EXECUTE sp_executesql @SQLStringUPDATE SET @intError = @@Error SET @intCount = @@ROWCOUNT IF @intError <> 0 BEGIN ROLLBACK TRANSACTION IF @intError = 2627 SET @strReturnMessage= 'Error: 2627' + CHAR(10) + CHAR(13) + 'Violation of PRIMARY KEY constraint.' + CHAR(10) + CHAR(13) + 'Cannot insert duplicate key.' ELSE SET @strReturnMessage='Errors were encountered during renaming from ' + @pstrOldValue + ' to ' + @pstrNewValue + CHAR(10) + CHAR(13) + ' Error: ' + CONVERT(VARCHAR, @intError) END ELSE BEGIN COMMIT TRANSACTION SET @strReturnMessage = 'Successfuly updated ' + CONVERT(varchar(20), @intCount) + ' record(s). Renamed from ' + @pstrOldValue + ' to ' + @pstrNewValue END ExitProc: BEGIN -- execute SQL_CHECK EXECUTE sp_executesql @SQLStringCheckConstraint RETURN END