Accessing 400 tables in a single query

I want to delete rows with a condition from mu开发者_如何学编程ltiple tables.

DELETE
FROM table_1
WHERE lst_mod_ymdt = '2011-01-01'

The problem is that, the number of table is 400, from table_1 to table_400. Can I apply the query to all the tables in a single query?

If you're using SQL Server 2005 and later you can try something like this (other versions and RDMS also have similar ways to do this):

DECLARE @sql VARCHAR(MAX)

SET @sql = (SELECT 'DELETE FROM [' + REPLACE(Name, '''','''''') + '] WHERE lst_mod_ymdt = ''' + @lst_mod_ymdt + ''';' FROM sys.tables WHERE Name LIKE 'table_%' FOR XML PATH(''))

--PRINT @sql;
EXEC ( @sql );

And as always with dynamic sql, remember to escape the ' character.

This will likely fall over if you have say table_341 which doesn't have a lst_mod_ymdt column.