How to run the same query against many tables

Sometimes you have many similar tables that all need to have the same query run against them. This can be a laborious chore, but with the help of a tool like NimbleText it can be a delight!

First paste your list of tables into the first textbox in NimbleText. Let's say our List of tables looks like this:

dbo.Customers
dbo.Customers_Archive
dbo.Sales
dbo.SalesDetail
dbo.Sales_Archive
dbo.SalesDetail_Archive

And we want to delete all records more than five years old.

Type a pattern like this into NimbleText:

try it →

DELETE
FROM 
    $0 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

Press 'Calculate' and hey presto:

DELETE
FROM 
    dbo.Customers 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

DELETE
FROM 
    dbo.Customers_Archive 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

DELETE
FROM 
    dbo.Sales 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

DELETE
FROM 
    dbo.SalesDetail 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

DELETE
FROM 
    dbo.Sales_Archive 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

DELETE
FROM 
    dbo.SalesDetail_Archive 
WHERE 
    CreatedDate < DateAdd(yyyy, -5, GetDate())

Copy it from NimbleText over to your favorite query analyzer, and KAPOW! All those old records gone before you can say "Just a minute, I meant 7 years, not 5."

And with a quick brain like yours you can see how the same technique can be applied to:

• Generate A Select Query Against Many Tables
• Update The Same Column In Many Tables
• Build A Union Across Many Tables
• Count The Number Of Rows In All Tables
• Group By Each Column Of A Table
• Add The Same Column To Many Tables
• Create Views For Many Tables
• Truncate Many Tables
• Create Audit Tables Based On Each Table

We've also written guides on many other ad-hoc queries you can generate:

• How to Generate Insert Statements
• How to Generate Update Statements
• How to Generate SQL 'IN' Statements
• How to List the Columns of a Table, Quickly!
• Extracting a list of columns from a block of text
• How to Generate Views the easy way

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

• General help
• Symbols and Keywords
• Built-in Functions
• The 'Where' clause
• Date Time formatting
• Date Time parsing
• Command-Line Automation
• SQL Master Class
• HTML Master Class
• Popular Text Manipulations Made Easy «« bookmark this one

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.