Deleting Specific tables with sp_MSforeachtable
-
Hi
I want to delete specific tables from database .Let us say I have to delete tables which names start with 'T'. Can I use sp_MSforeachtable for this. Since I want to avoid cursor here ,can I give list of specific tables for the procedure sp_MSforeachtable
Thanks
Sardar
-
Yes you can, you can give a pre-command and post-command and also a where clause to sp_MSforeachtable.
But... be aware that this proc is nothing else than a shorthand shortcut for writing your own cursor, so it's not giving you a way to avoid cursors.
On the other hand, I see no reason why you shouldn't use a cursor, this is one case that suits a cursor well. You do need to use dynamic SQL, though, but this sounds very much like an admin maintenance task(?) so it shouldn't be much of a concern then.
I'd probably just write my own cursor to pick out the tablenames I wanted and delete them.
/Kenneth
-
Another solution.. Generate the drop commands with a select query :
select 'Drop table dbo.' + Name as DropCommand from dbo.SysObjects where XType = 'U' and name like 'T%'
Once you have the right where condition in the select, paste the results in QA and run.
-
Remi,
he wants delete.. not drop
Though the idea is valid for deletes too
/Kenneth
-
I want to drop a table.
But I want to delete data.
That's the just way I hear it... but now he knows he can do both.
-
Sorry It was mistake ...Actually I want to drop the tables ....
Thanks for your reply ...
Sardar
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply