如何在MySQL中找到所有表中的特定列名?

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?

我有2-3个不同的列名，我想在整个DB中查找并列出所有有这些列的表。简单的脚本吗?

8 个解决方案

#1

1051

To get all tables with columns columnA or ColumnB in the database YourDatabase:

在数据库中的数据库中，获取所有列的列columnA或ColumnB:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

#2

118

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

#3

More simply done in one line of SQL:

只需在一行SQL中完成:

SELECT * FROM information_schema.columns WHERE column_name = 'column_name';

#4

SELECT DISTINCT TABLE_NAME, COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE column_name LIKE 'employee%'  
AND TABLE_SCHEMA='YourDatabase'

#5

In version that do not have information_schema (older versions, or some ndb's) you can dump the table structure and search the column manually.

在没有information_schema(旧版本，或一些ndb)的版本中，您可以转储表结构并手动搜索列。

mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql

Now search the column name in some_file.sql using your preferred text editor, or use some nifty awk scripts.

现在在some_file中搜索列名。使用您首选的文本编辑器，或者使用一些漂亮的awk脚本。

And a simple sed script to find the column, just replace COLUMN_NAME with your's:

一个简单的sed脚本来查找列，只需将COLUMN_NAME替换为您的:

sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql
USE `DATABASE_NAME`;
CREATE TABLE `TABLE_NAME` (
  `COLUMN_NAME` varchar(10) NOT NULL,

You can pipe the dump directly in sed but that's trivial.

您可以直接在sed中插入转储文件，但这是微不足道的。

#6

For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...

对于那些搜索这个逆的人，例如查找不包含某个列名称的表，这里是查询…

SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE 
TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT 
TABLE_NAME FROM information_schema.columns WHERE column_name = 
'column_name' AND TABLE_SCHEMA = 'your_db_name');

This came in really handy when we began to slowly implement use of InnoDB's special ai_col column and needed to figure out which of our 200 tables had yet to be upgraded.

当我们开始慢慢地使用InnoDB的特殊ai_col列时，这是非常有用的，并且需要计算出我们的200个表中哪一个还没有升级。

#7

If you want "To get all tables only", Then use this query:

如果您希望“仅获取所有表”，则使用以下查询:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'

If you want "To get all tables with Columns", Then use this query:

如果您希望“用列获取所有表”，则使用以下查询:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE column_name LIKE '%'  
AND TABLE_SCHEMA='tresbu_lk'

#8

Use this one line query, replace desired_column_name by your column name.

使用这一行查询，用您的列名替换desired_column_name。

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';

#1