I have one table with column ID that is reflected in multiple tables with primary key and foreign key relationship i have to delete that ID from multiple tables first i have to find in how may tables that id occurred based on that i have to delete that ID.
I have to search for column id value like 1 in table and related table also.
Adapting the code from here , run the following query in your database after replacing the table name and column name with your primary key table and the primary key column. The last column in the result will be a DELETE statements. Copying and executing those will delete the rows from the FK tables (assuming the schema is dbo. If not you will need to also include the schema name).
After you have done that, delete the row from the PK table.
SELECT o1.name AS FK_table, c1.name AS FK_column, fk.name AS FK_name, o2.name AS PK_table, c2.name AS PK_column, pk.name AS PK_name, 'DELETE FROM ' + o1.name + ' WHERE ' + c1.name + '= 1;' FROM sys.objects o1 INNER JOIN sys.foreign_keys fk ON o1.object_id = fk.parent_object_id INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id INNER JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id INNER JOIN sys.key_constraints pk ON fk.referenced_object_id = pk.parent_object_id AND fk.key_index_id = pk.unique_index_id WHERE o2.name = 'YourTableNameHere' -- Name of the PK table. AND c2.name = 'AlertName' -- column name in the PK table. ORDER BY o1.name, o2.name, fkc.constraint_column_id
If the foreign keys are set up with cascade delete, then you wouldn't need to do this. You can just delete the data from the PK table, and that will automatically delete the data from the FK tables as well. I am not a fan of cascading deletes. I like to know and see what I am deleting. So my recommendation would be that if it is not already set up with cascading deletes, leave it that way.
This is not working.