Delete from Multiple tables 1

[lobocheese]

Hi there, can this be done - here is the subquery that I'd like to use :
SELECT name
FROM dbo.sysobjects
WHERE (xtype = 'U' AND name LIKE 'd_ord%') AND (name NOT LIKE 'D_Ord_Act_Type')

Can this be incorporated into a Delete From clause?
if not there is only likely to be less than 100 tables fitting the criteria so I guess a loop would be acceptable too, thank-you!

 

[gmmastros]

No matter what you do, you need to loop. There is an undocumented sql server system stored procedure named sp_msforeachtable that you can use to delete all the rows in each table. However, even this stored procedure performs a loop within it.

I strongly encourage you to make a good backup before running the last command!

You can see which tables would be deleted with this command.

sp_msforeachtable '
    if ''?'' Like ''[[]dbo].[[]d_ord%'' 
        And ''?'' <> ''[dbo].[D_Ord_Act_Type]'' 
            Select ''?'''

If you want to delete all the rows in each of the tables...

sp_msforeachtable '
    if ''?'' Like ''[[]dbo].[[]d_ord%'' 
        And ''?'' <> ''[dbo].[D_Ord_Act_Type]'' 
            [!]Delete From ?[/!]'

If you have foreign keys configured for these tables, you may get errors when you run this. The order in which you delete rows from the tables matters.

If this is a 'once an done' situation, it may be easier/safer to create a query that generates the delete queries, like this...

SELECT [!]'Delete From ' + [/!]name
FROM dbo.sysobjects
WHERE (xtype = 'U' AND name LIKE 'd_ord%') AND (name NOT LIKE 'D_Ord_Act_Type')

When you run this query, you will see a list of delete statements in the output window. You can then copy/paste to a new query window, validate that the correct tables are returned, and then run the queries.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lobocheese]

perfect, thank-you gmmastros! great little undocumented feature sp_msforeachtable - I've just been looking it up.