How to find all columns where the column name is longer than 5?

Question

I have a database called bbs that contains 37 tables. I want to find all the columns in these tables where the column name is longer than 5!

mysql> show tables; +---------------------+ | Tables_in_rails_bb | +---------------------+ | articles | | articles_categories | | bookmarks | | categories | | comments | | drafts | | extension_groups | | extensions | | forum_tracks | | forums | | icon_items | | icons | | levels | | management_groups | | management_logs | | message_folders | | message_tos | | messages | | moderators | | posts | | replies | | reports | | roles | | roles_users | | schema_migrations | | sessions | | smiles | | subscribes | | system_configs | | topic_tracks | | topics | | upload_files | | users | | users_forums | | users_topics | | warnings | | word_replacements | +---------------------+ 37 rows in set (0.25 sec) 

How to write sql?

Answer 1

The only thing you change here is your database name ( Table_Name , Column_Name fixed). try the following:

 SELECT Table_Name, Column_Name FROM information_schema.columns WHERE table_schema = 'databaseName' -- <= Database Name Here HAVING CHAR_LENGTH(COLUMN_NAME) > 5 ORDER BY Table_Name, Column_Name 

or you can also select all fields

 SELECT * FROM information_schema.columns WHERE table_schema = 'databaseName' -- <= Database Name Here HAVING CHAR_LENGTH(COLUMN_NAME) > 5 ORDER BY Table_Name, Column_Name 

Answer 2

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE CHAR_LENGTH(COLUMN_NAME) > 5 AND TABLE_SCHEMA='YourDatabase'; 

Not tested! Found this question and edited the request. Something like this should at least get you started :)

Answer 3

Just query the information_schema database:

 mysql> connect information_schema; mysql> select table_name, column_name from columns where table_schema = 'bbs' and char_length(column_name) > 5; 

Note that char_length (str) will give you the number of str characters, and length (str) will result in size in str bytes.