从特定数据库中检索所有表名，列名和列值

I'm trying to select all table names, column names, column values and display them with a single line group by table name. Here is the query.

我正在尝试选择所有表名，列名，列值，并按表名用单个行组显示它们。这是查询。

SELECT 
    CONCAT(a.table_name,'-',GROUP_CONCAT(a.column_name,':' 
                                         ORDER BY ordinal_position ASC)) 
    as table_data
FROM
(
    SELECT * FROM information_schema.columns WHERE table_schema = 'db_xxx'
) a
GROUP BY table_name

The above query works just fine to retrieve table names and column names but when I trying to get its column data with

上面的查询可以很好地检索表名和列名，但是当我试图获取其列数据时

SELECT 
    CONCAT(a.table_name,'-',GROUP_CONCAT(a.column_name,':',
                            (SELECT a.column_name FROM a.table_name) 
                            ORDER BY ordinal_position ASC)) 
    as table_data
FROM
(
    SELECT * FROM information_schema.columns WHERE table_schema = 'db_xxx'
) a
GROUP BY table_name

It could not identify a.table_name in (SELECT a.column_name FROM a.table_name). How should I fix this?

它无法识别（SELECT a.column_name FROM a.table_name）中的a.table_name。我该怎么解决这个问题？

1 个解决方案

#1

To get table_name and column names you could use:

要获取table_name和列名称，您可以使用：

SELECT
 CONCAT(`table_name`, '-',
    GROUP_CONCAT(`column_name` ORDER BY ordinal_position SEPARATOR ':')) AS result
FROM information_schema.columns
-- WHERE `table_name` = ?
GROUP BY `table_name`;

SqlFiddleDemo

SqlFiddleDemo

Output:

输出：

╔══════════════╗
║    result    ║
╠══════════════╣
║ tab0-id:col  ║
║ tab1-id:col2 ║
║ tab2-id:col3 ║
╚══════════════╝

---

To get sample record for each column you need to use dynamic-sql:

要获取每列的样本记录，您需要使用dynamic-sql：

SET @sql = '';

SELECT @sql := CONCAT(@sql, 
                      '(SELECT ''', `table_name`, ''' AS tab_name, ''', 
                                 `column_name`, ''' AS column_name, ',
                                 `ordinal_position`, ' AS ordinal_pos, ',
                                 `column_name`, ' AS sample '
                       'FROM ', `table_name` ,         
                       ' LIMIT 1) UNION ALL ')
FROM information_schema.columns
WHERE `table_name` LIKE 'tab_';

SET @sql := SUBSTRING(@sql, 1, LENGTH(@sql) - 11);

SET @final_sql = 
'SELECT CONCAT(`tab_name`, ''-'',
    GROUP_CONCAT(CONCAT(`column_name`, '':'', sample ) ORDER BY ordinal_pos)) 
    AS result
 FROM ( <placeholder> ) AS sub
 GROUP BY tab_name';

SET @final_sql = REPLACE(@final_sql, '<placeholder>', @sql);

prepare s from @final_sql;
execute s;

deallocate prepare s;

SqlFiddleDemo2 SqlFiddleDemo3

SqlFiddleDemo2 SqlFiddleDemo3

Output:

输出：

╔════════════════════════════╗
║           result           ║
╠════════════════════════════╣
║ tab0-id:1,col:a            ║
║ tab1-id:10,col2:2          ║
║ tab2-id:20,col3:2016-01-26 ║
╚════════════════════════════╝