0

I have 203 tables in my SQL database, I want to print the latest records for each table. I know the query to get the latest row of one table at one time. How do I query for the latest row of each table in one go?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CodeLearner
  • 15
  • 5
  • 1
    What is your database engine? – Kevin Aug 22 '22 at 07:14
  • 4
    How do you determine what the "latest row" is in each table? Remember that database engines _don't actually_ keep track of the last-written-row in each table (surprisingly, there's a _lot_ of things that RDBMS _don't do_, even today...) so the only way to do that _correctly_ is by having a `rowversion` column or be using a temporal-table (with `PERIOD FOR SYSTEM_TIME` columns), or have a meticulously designed DB with triggers to ensure last-modified timestamps are always correct. Now, you could check for the `MAX` value in an `IDENTITY` (aka `AUTO_INCREMENT`) but that doesn't track `UPDATE`. – Dai Aug 22 '22 at 07:19
  • what is you query for latest record of one table at one time ? you want to get 1 latest result from all table at a time ? – Aravind Aravind Aug 22 '22 at 07:33
  • You need one column is same for all table because of order class need to add – Aravind Aravind Aug 22 '22 at 07:47

2 Answers2

1

Here I do not know what are the names of your DB. So I assume that they can be indexed in the way I am about to show:

DECLARE @Counter INT
SET @Counter=1
WHILE ( @Counter <= 203)
BEGIN
    EXEC('SELECT TOP(5) * FROM TABLE_'+@Counter+'ORDER BY Date DESC')
    SET @Counter  = @Counter  + 1
END

Here make sure that you have defined everything using dynamic queries. In addition, I did not know in what format you need your pulled results to look.

MURTUZA BORIWALA
  • 500
  • 3
  • 8
0

Use SHOW TABLES and GROUP CONCAT and

SET @Expression = SELECT CONCAT('SELECT...
                  SELECT GROUP_CONCAT(...
PREPARE myquery FROM @Expression;
EXECUTE myquery;

Is it possible to execute a string in MySQL?

Motomotes
  • 4,111
  • 1
  • 25
  • 24