Delete rows from multiple tables

[pathaide]

Hi there

I am in need of creating a subset of our data. I would like to delete all rows from all tables that have a date earlier than 2007-01-01. Does anyone know of a simple way to do this?

I was hoping a simple statement like the one below would do the trick

delete from (select table_name from rsys_tables)
where rn_create_date < '2007-01-01'

Any help would be appreciated

Thanks

 

[Crowley16]

you'd have to use dynamic sql to do something like that...

--------------------
Procrastinate Now!

 

[pathaide]

any thoughts on syntax for that? I'm having problems finding something similar

 

[bborissov]

Try:

sp_MSForEachTable 'DELETE FROM ? WHERE  rn_create_date < ''2007-01-01'''

It will bombs for every table that does not have such field "rn_create_date", but I think this should work, not tested though.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[markros]

You can add TRY/CATCH in SQL 2005

http://www.databasejournal.com/features/mssql/article.php/3587891

 

[bborissov]

Yep,
but there is no point of version pathaide use.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pathaide]

Thanks guys....I am using SQL2000 and currently running the statement that Borislav gave me....if I have enough disk space for it to complete I think I'll be ok. I'll update this shortly to let you know how I made out

Thanks