mysql - 列出具有特定列名的列值的所有表名

Question

谁能帮我解决这个问题。我需要列出所有表名以及列名“Extract_Date”中的最大日期值。我下面的查询只返回所有表名的列表，其中有列名 'Extract_Date' 但我不确定如何列出列值。非常感谢。

select
    object_schema_name(t.object_id) + '.' + t.name as table_name,
    c.name as column_name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where c.name like '%extract_date%'

Answer 1

试试这个

SELECT TABLE_NAME,MAX(COLUMN_NAME) FROM information_schema.columns
 WHERE table_name IN (SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='your_database_name') AND COLUMN_NAME = 'extract_date'