Find all tables with specific column name

Find all tables with specific column name

Ever need to find out if or where a column exists within one of your tables? MSSQL and MySQL are remarkably very similar with at least one strange difference:

-- MYSQL
SELECT COLUMN_NAME, TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('club','ClubID')
AND TABLE_SCHEMA = 'mydatabase';

If you want to limit the filter to just one database (assuming multiple databases), youโ€™ll filter upon TABLE_SCHEMA within MySQL but TABLE_CATALOG within MSSQL. The rest is pleasingly the same information schema.

-- MSSQL
SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME IN ('club','ClubID')
AND TABLE_CATALOG = 'mydatabase';

Alter the WHERE statement to use a wildcard if you need to perform a LIKE search.

Earlier versions of MySQL do not have INFORMATION_SCHEMA. Youโ€™ll need to dump the table structure to a flat file and perform a text search.

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
Leave a comment

Your email address will not be published. Required fields are marked *