Complete the query for all tables and fill in the data in the new

Question

I need to get a result table with such fields - table_name, min_date, max_date

Here is my query that I have to execute for all tables

SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM testTable GROUP BY short_date HAVING COUNT(*) > 1) as Duplicates 

Then I found out how to get all the table names. I do it this way.

 SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%' 

But I do not know how to execute it for the whole table and fill the result in a new table.

I tried to do it this way

 DECLARE @DB_Name varchar(50) DECLARE @Command varchar(100); DECLARE database_cursor CURSOR FOR SELECT name FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%') as TableNames OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM ' + @DB_Name + ' WHERE type = ''test'' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates' EXEC sp_executesql @Command FETCH NEXT FROM database_cursor INTO @DB_Name END CLOSE database_cursor DEALLOCATE database_cursor 

But I got this error

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that matches the version of your MySQL server. for the correct syntax to use next to 'DECLARE @DB_Name varchar (50) DECLARE @Command varchar (100)' on line 1

UPD

 CREATE PROCEDURE GetData() BEGIN DECLARE @DB_Name varchar(50), @Command varchar(100); DECLARE database_cursor CURSOR FOR SELECT name FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%_') as TableNames OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM ' + @DB_Name + ' WHERE type = ''test'' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates' EXEC sp_executesql @Command FETCH NEXT FROM database_cursor INTO @DB_Name END; CLOSE database_cursor DEALLOCATE database_cursor END; CALL GetData() 

Answer 1

Add DELIMITER $$ at the beginning; add DELIMITER ; after END .

Get rid of the team announcement. Use @command , which does not need to be declared.

Add SELECT @command; after SELECT @command := ...; so that we can do some debugging.

The CLOSE and DEALLOCATE need ; to complete them.

Test to execute lines before FETCH .

You really need to look at a few examples of stored procedures, especially with cursors.

Update

Ugh, I have not noticed even half of the syntax errors. This may work (I cannot say because I do not have your specific tables or columns.):

 DROP PROCEDURE IF EXISTS so42856538; DELIMITER $$ CREATE PROCEDURE so42856538() LANGUAGE SQL MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN DECLARE _TableName varchar(64); DECLARE _done INT DEFAULT FALSE; DECLARE database_cursor CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` LIKE 'test%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE; OPEN database_cursor; curs_loop: LOOP FETCH NEXT FROM database_cursor INTO _TableName; IF _done THEN LEAVE curs_loop; END IF; SET @Command := CONCAT( 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM ( SELECT short_date, type, value, count(*) as cnt FROM ', _TableName, ' WHERE type = "test" GROUP BY short_date, type, value HAVING COUNT(*) > 1 ) as Duplicates' ); SELECT _TableName, @command; -- Debugging (remove if it is clutter) PREPARE _sql FROM @command; EXECUTE _sql; DEALLOCATE PREPARE _sql; END LOOP; CLOSE database_cursor; END $$ DELIMITER ; CALL so42856538; 

Answer 2

right ... you can try this.

delete this line and run the query again.

 DECLARE @DB_Name varchar(50), @Command varchar(100); 

I think in mysql you can just use a variable without declaring it and then throw it if necessary.

Answer 3

As far as I know about this error, due to DELIMETER and something else you need to correctly apply the cursor to repeat whole rowset . Here in the lower code, I recorded procedure using CURSOR . Create a dynamic query and execute this dynamic query using

 PREPARE stmt FROM @VAR_QRY; EXECUTE stmt; 

This dynamic query will return you an accurate result. I assumed that you have basic knowledge of trigger , loop and CURSOR

Try entering the code below. Hope this helps.

 DROP PROCEDURE IF EXISTS ITERATEALLTABLE; DELIMITER $$ CREATE PROCEDURE ITERATEALLTABLE() BEGIN DECLARE VAR_TABLE varchar(100); DECLARE VAR_QRY varchar(100); DECLARE VAR_FINISHED INT(11) DEFAULT 0; DECLARE DATABASE_CURSOR CURSOR FOR SELECT name AS TableNames FROM ( SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%' )Z ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET VAR_FINISHED = 1; OPEN DATABASE_CURSOR; GET_NEXTRECORD: LOOP FETCH DATABASE_CURSOR INTO VAR_TABLE; IF VAR_FINISHED = 1 THEN LEAVE GET_NEXTRECORD; END IF; SET @VAR_QRY = CONCAT("SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM " , VAR_TABLE , " WHERE type = 'test' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates"); PREPARE stmt FROM @VAR_QRY; EXECUTE stmt; END LOOP GET_NEXTRECORD; CLOSE DATABASE_CURSOR; END; CALL ITERATEALLTABLE;