CODEWITHSUNDEEP
mysql
neha prajapati
neha prajapati

Reputation: 69

resultset of particular query

I have this query. When I execute I got Query statement but I want result set of this query.

My query is: 

SELECT CONCAT('select * from ', table_name, ' union all ') AS CUSTOMQUERY
FROM   information_schema.tables
WHERE  table_name LIKE '%custom%'

I get this result 

select * from custom_70006 union all  
select * from custom_704306 union all  
select * from custom_700436 union all  
select * from custom_7000643 union all  
select * from custom_7000634 union all  
select * from custom_700046 union all  
select * from custom_700063 union all  
select * from custom_700062 union all

But I want result set of this particular column with corresponding data and last union all should remove.

Please help me with relevant query.

Upvotes: 1

Views: 133

Answers (1)

Omesh
Omesh

Reputation: 29091

I think this is what you are looking for:

SET GLOBAL group_concat_max_len = 4294967295;

SELECT @query1 := GROUP_CONCAT(CONCAT('SELECT * FROM ', table_name) SEPARATOR
                         ' UNION ALL ') AS CUSTOMQUERY
FROM   INFORMATION_SCHEMA.tables
WHERE  TABLE_SCHEMA = SCHEMA()
       AND table_name LIKE '%custom%';


PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Example: SQLFiddle

or modify your query as:

SELECT @query1 := CONCAT('select * from ', table_name, ' union all ') AS
       CUSTOMQUERY
FROM   information_schema.tables
WHERE  table_name LIKE '%custom%'

to remove last 'union all' string from your query:

SET @query1 = TRIM(TRAILING 'union all' FROM TRIM(@query1));

PS: Default value of group_concat_max_len is only 1024. So you need to set it to higher value otherwise the output of your query will get stripped and you may get syntax error.

Upvotes: 1

Related Questions