How can I find all columns where the column name length is greater than 5?

Question

I have a database named bbs, which have 37 tables. I want to find all columns in these tables where the column name length is greater 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 the sql?

try my solution below. Hope that is what you are looking for. — John Woo, Feb 12 '12 at 16:20
yes, its a rails bbs app! i want to add prefix to some column but i should check which way is the best? change db or change my code. thank you for your reply^_^ — neocanable, Feb 12 '12 at 17:38

score 2 · Answer 1 · edited May 23 '17 at 11:59

2

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

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

edited May 23 '17 at 11:59

Community

1
1

answered Feb 12 '12 at 16:16

Ranhiru Jude Cooray

19,542
20
83
128

John Woo · Accepted Answer · 2012-02-12T16:49:44.193

The only thing you will change here is the name of your database (Table_Name, Column_Name are fixed). try this one:

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

score 1 · Answer 3 · answered Feb 12 '12 at 16:44

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;

Take into account that char_length(str) will give you the amount of characters str have while length(str) will result in the size in bytes of str.

How can I find all columns where the column name length is greater than 5?

3 Answers3