NimbleText

Manipulate text and data with light-weight patterns

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:

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.

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.

Download NimbleText