sql server - How to delete particular common record from all tables in particular schema (T SQL)?

Question

I have tables in a database with three schemas (schema1, schema2, schema3). Each schema has 2 tables (schema1.table1, schema1.table2, schema2.table1, schema2.table2........) each table has ID column which has values like 17, 18, 19, 20, 50 etc. I want to delete record 19 from all tables in schema1, something like > DELETE ID 19 from ALL tables in Schema1. Is there any way to do that? Appreciate any help Thanks

question from:https://stackoverflow.com/questions/65852841/how-to-delete-particular-common-record-from-all-tables-in-particular-schema-t-s

Answer 1

Sure it could be done by Dynamic sql, you will get all your needed table into a temp table with an identity column [id].

CREATE TABLE #temp ---identity column will be used to iterate
(
id INT IDENTITY,
SchemaName VARCHAR(20),
TableName VARCHAR(20),
ColumnName VARCHAR(20)
)
INSERT INTO #temp
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
-- choose your own results with where conditions

DECLARE @SQL VARCHAR(MAX)
DECLARE @Count INT = 1
DECLARE @Schema VARCHAR(20)
DECLARE @Table VARCHAR(20)
DECLARE @Column VARCHAR(20)

WHILE @COUNT <= (SELECT COUNT(*) FROM #temp) 
BEGIN
    SELECT @Schema = SchemaName FROM  #temp WHERE id = @Count
    select @table = TABLENAME FROM #temp WHERE id = @Count
    SELECT @Column = COLUMNNAME FROM #temp WHERE id = @Count
    
    SELECT @sql = 'DELECT FROM '+@Schema+'.'+@Table+ 'WHERE ID = 19'

    --PRINT @SQL
SET @Count = @Count + 1

END

change PRINT to Exec if your printed query looks good to you.