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
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
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.Download NimbleText