Kasun NanayakkaraFind the tables that belong to the column name. Show the primary tables and foreign key tables as a list here.
SELECT c.name AS 'ColumnName'
,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyColumnName%'
ORDER BY TableName
,ColumnName;
ColumnName TableName
MyColumnName dbo.FkTable1
MyColumnName dbo.FkTable2
MyColumnName dbo.MyTable
The task here is to update data in MyTable. In this table, I need to change the records code with primary keys 1, 2, and 3, and update other records primary keys as well. Additionally, I need to verify the current primary keys of the records and correct them if necessary.I will also need to check the current structure of MyTable and update the structure accordingly. After making the updates, the identity seed should be reset.
Current Mytable
id code name
1 abc abc-disc
2 efg efg-disc
3 ijh ijh-disc
20 kmn kmn-disc
30 qrs qrs-disc
Udated Mytable
id code name
1 cba abc-disc
2 gfe efg-disc
3 hji ijh-disc
100000000 kmn kmn-disc
100000001 qrs qrs-disc
Stop the current identity check
SET IDENTITY_INSERT Mytable ON
GO
Declare a table parameter to capture the current data before making any changes to the MyTable, so that you can keep the original data intact and then modify it accordingly. This is a common approach to ensure data integrity during updates. You can create a table variable or temporary table to store the original data before making any changes.
DECLARE @TempMytable TABLE (ID INT, Code VARCHAR(MAX), Name VARCHAR(MAX), NewRecordID INT);
Insert Mytable record to tmp table call TempMytable.
INSERT INTO @TempMytable(ID, Code, Name)
SELECT ID, Code, Name
FROM Mytable;
DECLARE @ID INT, @Code VARCHAR(MAX), @Name VARCHAR(MAX)
Set the default identity seed value and other related parameter values.
DECLARE @Seed INT= 10000000, @NewRecordID INT = 1, @NewCode VARCHAR(MAX) = '';
Disable the check constraint.
ALTER TABLE MPP NOCHECK CONSTRAINT [FK_MPP_Mytable];
ALTER TABLE Mytable NOCHECK CONSTRAINT ALL;
Remove the unique constraint.
ALTER TABLE Mytable
DROP CONSTRAINT U1_Mytable, UK_Mytable_Name;
Disable the trigers
ALTER TABLE MyTable DISABLE TRIGGER Mytable_InsteadOfDTrig
Declare the cursor and refer to the records available in MyTable because I created a temporary table. When the cursor runs, it gets the current table information. If the table is updated, the record will also be fetched and processed. So, I want to avoid that behavior and run based on the previous (old) state of the table and data.
DECLARE db_cursor CURSOR FOR
SELECT ID, Code, Name
FROM @TempMytable
BEGIN
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ID, @Code, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Name = 'abc-disc' OR @Name = 'efg-disc' OR @Name = 'ijh-disc'
BEGIN
/* Insert / Update system code */
IF @Name = 'abc-disc'
SELECT @NewRecordID = 1, @NewCode = 'cba';
ELSE IF @Name = 'efg-disc'
SELECT @NewRecordID = 2, @NewCode = 'gfe';
ELSE IF @Name = 'ijh-disc'
SELECT @NewRecordID = 3, @NewCode = 'hji';
IF @ID = @NewRecordID
BEGIN
UPDATE Mytable SET Code = @NewCode WHERE ID = @NewRecordID
END
ELSE IF EXISTS(SELECT * FROM Mytable WHERE ID = @NewRecordID)
BEGIN
UPDATE Mytable SET Code = @NewCode,
Name = @Name
WHERE ID = @NewRecordID
END
ELSE
BEGIN
INSERT INTO Mytable(ID, Code, Name)
VALUES(@NewRecordID, @NewCode, @Name)
END
END
In here, I want to check whether records already exist in your table before inserting new ones, so you can avoid inserting duplicate records that would violate a unique constraint.
ELSE IF(@ID < 10000000)
/* Check table id morethan the 10,000,000 */
BEGIN
/* Insert record as a new record Mytable */
INSERT INTO Mytable(ID, Code, Name)
VALUES(@Seed, @Code, @Name)
SELECT @NewRecordID = @Seed;
SELECT @Seed = @Seed + 1;
END
In this case, I need to get the new record ID into a temporary table called @TempMyTable to check if the records have new IDs
/* Update @TempMytable tables with new ID */
UPDATE @TempMytable
SET NewRecordID = CASE WHEN @ID > @NewRecordID THEN @ID
ELSE @NewRecordID
END
WHERE ID = @ID;
PRINT CAST(@ID AS VARCHAR) +' - ' + @Code +' - ' + @Name +' - ' + CAST(@NewRecordID AS VARCHAR) + ' - ' + @NewCode
FETCH NEXT FROM db_cursor
INTO @ID, @Code, @Name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Reset @id , @NewRecordsID values
SELECT @ID = 0, @NewRecordID = 0;
/* Verify date at @TempMytable table and foreign key tables */
SELECT * FROM @TempMytable
id code name newRecordid
1 abc abc-disc 1
2 efg efg-disc 2
3 ijh ijh-disc 3
20 kmn kmn-disc 100000000
30 qrs qrs-disc 100000002
Check if foreign key tables have records.
SELECT m.ID,Count(m.pkid) FkTable1_Count FROM FkTable1 m GROUP BY m.ID
SELECT m.ID,Count(m.pkid) FkTable2_Count FROM FkTable2 m GROUP BY m.ID
FkTable1
id FkTable1_Count
1 1170
2 5522
FkTable2
id FkTable2_Count
1 15877
2 155895
3 187958
Declare another cursor and refer to the records available in the temporary table called @TempMyTable to update the new record ID.
DECLARE db_newCursor CURSOR FOR
SELECT ID, NewRecordID
FROM @TempMytable
BEGIN
OPEN db_newCursor
FETCH NEXT FROM db_newCursor INTO @ID, @NewRecordID
WHILE @@FETCH_STATUS = 0
BEGIN
Update the foreign key value as per the newly generated record ID.
/* Update foreign key tables*/
IF @ID = @NewRecordID
If record id s is same no need to change.
PRINT 'IDs are same. '+ CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
ELSE
BEGIN
PRINT 'FkTable1 # ' + CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
UPDATE FkTable1 SET ID = @NewRecordID
WHERE ID = @ID;
PRINT 'FkTable2 # ' + CAST(@ID AS VARCHAR) + ' -> ' + CAST(@NewRecordID AS VARCHAR);
UPDATE FkTable2 SET ID = @NewRecordID
WHERE ID = @ID;
END
FETCH NEXT FROM db_newCursor INTO @ID, @NewRecordID
END
CLOSE db_newCursor
DEALLOCATE db_newCursor
END
start the current identity check
SET IDENTITY_INSERT Mytable OFF
GO
Why does the previous state not delete the record? Because it has a foreign key value, and it will be affected when I delete the record. I know the old records have a range from 4 to 99999999, and those records should be deleted in the delete query.
/* Delete old Mytable */
DELETE FROM Mytable WHERE ID BETWEEN 4 AND 9999999
Set up the identity seed value to match the maximum record value.
Generally, users reset the identity seed using this snippet:
DBCC CHECKIDENT ('MyTable', RESEED, @IdentSeed).
However, when I use it, I get the following error:
Checking identity information: current identity value '52228', current column value '52228'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I used the keyword WITH NO_INFOMSGS; to overcome the issue.
This problem is described in the following link: identity-column-value-falling-behind-randomly
/* Set new identity seed */
DECLARE @IdentSeed INT = 10000000;
SELECT @IdentSeed = Max(ID) FROM Mytable;
PRINT 'New identity seed # ' + CAST(@IdentSeed AS VARCHAR);
DBCC CHECKIDENT ('Mytable', RESEED, @IdentSeed) WITH NO_INFOMSGS;
GO
/* Add uniqe constraint */
SET ANSI_PADDING ON
GO
Add a unique key constraints for table records.
/****** Object: Index [Mytable] */
ALTER TABLE [dbo].[Mytable] ADD CONSTRAINT [U1_Mytable] UNIQUE NONCLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER INDEX [U1_Mytable] ON [dbo].[Mytable] DISABLE
GO
PRINT 'Added constrant - U1_Mytable';
/****** Object: Index [UK_Mytable_Name] */
ALTER TABLE [dbo].[Mytable] ADD CONSTRAINT [UK_Mytable_Name] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
PRINT 'Added constrant - UK_Mytable_Name';
ALTER TABLE MPP CHECK CONSTRAINT [FK_MPP_Mytable]
GO
ALTER INDEX ALL ON Mytable REBUILD
GO
Enable the trigers
ALTER TABLE MyTable ENABLE TRIGGER MyTable_InsteadOfDTrig
GO
