Help with using spMSforeachtable

I need to occasionally drop some table within a database that is has a naming convention of a datestamp...

EX: tb_trans_20070516

The following is what I have written but and I am able to select the correct tables that I wish to drop leaving only the most recent "keep two days worth".

exec sp_MSforeachtable
@.command1 = "select '?'",
@.whereand = "and name LIKE 'tb_trans_2%' and SUBSTRING(name, 10, 8) <= convert(character(8), GETDATE() -2, 112)"

My problem becomes when I try to execute as a DROP statement as such...

exec sp_MSforeachtable
@.command1 = "DROP TABLE '?'",
@.whereand = "and name LIKE 'tb_trans_2%' and SUBSTRING(name, 10, 8) <= convert(CHARACTER(8), GETDATE() -2, 112)"

What am I doing wrong?
May be you need "DELETE FROM TABLE"? Because DROP command deletes table and doesn't have any filtering, but DELETE FROM Table deletes data from table and has WHERE clause.|||SQL 2005 seems to need o.name in place of name

to make the drop work remove the quotes from around the ? ("DROP TABLE ?")|||

Yes.. Remove the single quote around the ?

sp_MSforeachtable is undocumented sp. Be catious when you develop with this sp, in future this sp may be removed without any notice.

Code Snippet

Exec sp_MSforeachtable

@.command1 = "DROP TABLE ?",

@.whereand = "and name LIKE 'tb_trans_2%' and SUBSTRING(name, 10, 8) <= convert(CHARACTER(8), GETDATE() -2, 112)"

|||

A couple of things here:

1. You generally should never put data into object names like this. For a temporary table, it isn't horrible, but if you are doing this regularly, it is usually better to just have a column of "date" and delete where date ='20070516'

2. It seems very odd to have a blanket statement like this to go to All databases and drop tables like this.

Of course, I don't know your exact situation, but these are very typical general guidlines.

|||Thx DaleJ,

Sometimes it just takes another set of eyes I guess? I know that this has worked for me in the past and just couldn't figure out what went wrong.

Thx Again,
acmcdba68