MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ztable1 (
  EMPLOYEE_ID VARCHAR(255)
);
INSERT INTO ztable1 VALUES
 (012345),('001234'),('123456    ');
CREATE TABLE ztable2 (
  EMPLOYEE_ID VARCHAR(255)
);
INSERT INTO ztable1 VALUES
 (054321),('004321'),('  654321');

CREATE TABLE ztable3 (
  EMPLOYEE_ID VARCHAR(255)
);
INSERT INTO ztable1 VALUES
 (888888);

Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable1 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable2 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable3;

SELECT * FROM
(SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable1 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable2 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable3) a
  WHERE lenEmpId <> 6;

SELECT * FROM
(SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable1 
    WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable2 
    WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) FROM ztable3
    WHERE LENGTH(EMPLOYEE_ID) <> 6) a;


EMPLOYEE_ID
LENGTH(EMPLOYEE_ID)


12345
5

001234
6

123456
10

54321
5

004321
6

  654321
8

888888
6




EMPLOYEE_ID
lenEmpId


12345
5

123456
10

54321
5

  654321
8




EMPLOYEE_ID
LENGTH(EMPLOYEE_ID)


12345
5

123456
10

54321
5

  654321
8


/*build the query*/
SELECT CONCAT('SELECT * FROM ( \n',
          GROUP_CONCAT(
             CONCAT('SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ',TABLE_NAME
                         ,' WHERE LENGTH(EMPLOYEE_ID) <> 6') SEPARATOR ' UNION ALL \n'),
            ') a;') INTO @myquery
   FROM INFORMATION_SCHEMA.TABLES
WHERE /*TABLE_SCHEMA = 'mySchema' AND */ TABLE_NAME like 'z%';

/*check the query*/
SELECT @myquery;

/*use prepare statement to execute then deallocate the query*/
PREPARE stmt FROM @myquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


@myquery


SELECT * FROM ( 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable1 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable2 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable3 WHERE LENGTH(EMPLOYEE_ID) <> 6) a;



Statement prepared




EMPLOYEE_ID
lenEmpId


12345
5

123456
10

54321
5

  654321
8


/*no need to change the query if there are new tables added*/
CREATE TABLE ztable4 (
  EMPLOYEE_ID VARCHAR(255)
);
CREATE TABLE ztable5 (
  EMPLOYEE_ID VARCHAR(255)
);
CREATE TABLE ztable6 (
  EMPLOYEE_ID VARCHAR(255)
);
CREATE TABLE ztable7 (
  EMPLOYEE_ID VARCHAR(255)
);
/*same query*/
SELECT CONCAT('SELECT * FROM ( \n',
          GROUP_CONCAT(
             CONCAT('SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ',TABLE_NAME
                         ,' WHERE LENGTH(EMPLOYEE_ID) <> 6') SEPARATOR ' UNION ALL \n'),
            ') a;') INTO @myquery
   FROM INFORMATION_SCHEMA.TABLES
WHERE /*TABLE_SCHEMA = 'mySchema' AND */ TABLE_NAME like 'z%';

/*check the query*/
SELECT @myquery;

/*use prepare statement to execute then deallocate the query*/
PREPARE stmt FROM @myquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


@myquery


SELECT * FROM ( 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable1 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable2 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable3 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable4 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable5 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable6 WHERE LENGTH(EMPLOYEE_ID) <> 6 UNION ALL 
SELECT EMPLOYEE_ID, LENGTH(EMPLOYEE_ID) AS lenEmpId FROM ztable7 WHERE LENGTH(EMPLOYEE_ID) <> 6) a;



Statement prepared




EMPLOYEE_ID
lenEmpId


12345
5

123456
10

54321
5

  654321
8