Menu
I have 2-3 different column names that I want to look up in the entire database and list out all tables which have those columns. Is there any easy script?
939
How to list all tables that contain a specific column name in MySQL? You want to look for tables using the name of columns in them. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME IN('column1', 'column2') AND TABLE_SCHEMA = 'schema_name';
select table_name from all_tab_columns where column_name = 'PICK_COLUMN'; If you've got DBA privileges, you can try this command instead: select table_name from dba_tab_columns where column_name = 'PICK_COLUMN'; Now if you're like me, you may not even know what the column you're searching for is really named.
In MySQL, there are two ways to find the names of all tables, either by using the "show" keyword or by query INFORMATION_SCHEMA. In the case of SQL Server or MSSQL, You can either use sys. tables or INFORMATION_SCHEMA to get all table names for a database.
To get all tables with columns columnA or ColumnB in the database YourDatabase:
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase'; 
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%wild%'; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
