Delete from multiple tables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Delete from multiple tables

Hello, I have more than 30 tables and I would like to delete records by running one query… delete from table where date =’6/6/2005′ every table has an ending with TER..for example aaaTER, bbbTER Can somebody help me with that Thanks
If you have common columns then create FKs with cascade delete option enabled.
This can be retained I beleive meaning FKs will protect your data integrity.
Other method would be to write a proc and dynamically build the delete stmt.

Run this Select ‘Delete from ‘+Table_Name + ‘ where date =”6/6/2005”’
from information_Schema.Tables where Table_Name like ‘%TER’
and Table_Type=’Base Table’ Copy the result back to Query Analyser and run them one by one Madhivanan Failing to plan is Planning to fail
Do you need this on a regular basis or this just a one time action? —
Frank Kalis
Microsoft SQL Server MVP
Ich unterstütze PASS Deutschland e.V.

Ensure database transaction log is not blown during this operation, so watch its space. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
thanks guys… I actually run this every month… Thanks Madhivanan, that was really helpful