How to find column data in multiple tables

Transact-SQL
1

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.

2

can you try this
select c.column_name,o.name as TableName from information_schema.columns c
inner join sys.objects o
on c.table_name=o.name
where o.type='U'
and c.name='ID'

3

I have to search for column id value like 1 in table and related table also.

4

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.

5

This is not working.