MySQL-এ INFORMATION_SCHEMA দিয়ে TEXT টাইপের কলামগুলো ফিল্টার করা

MySQL-এ ডেটাবেসের সব টেবিল থেকে শুধু TEXT ডেটা টাইপের কলামগুলো খুঁজে বের করতে INFORMATION_SCHEMA.COLUMNS টেবিল কোয়েরি করা হয়।

সিনট্যাক্স

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'text';

ফলাফল (আংশিক - ২৪০ রো)

+---------------------------------------------+--------------------------------+
| TABLE_NAME                                  | COLUMN_NAME                    |
+---------------------------------------------+--------------------------------+
| COLUMNS                                     | COLUMN_DEFAULT                 |
| COLUMNS                                     | COLUMN_COMMENT                 |
| FILES                                       | FILE_NAME                      |
| PARTITIONS                                  | PARTITION_DESCRIPTION          |
| PARTITIONS                                  | PARTITION_COMMENT              |
| ROUTINES                                    | ROUTINE_COMMENT                |
| replication_group_member_stats              | LAST_CONFLICT_FREE_TRANSACTION |
| password_history                            | Password                       |
| component                                   | component_urn                  |
| innodb_buffer_stats_by_schema               | object_schema                  |
| innodb_buffer_stats_by_schema               | allocated                      |
| innodb_buffer_stats_by_schema               | data                           |
| x$innodb_buffer_stats_by_schema             | object_schema                  |
| innodb_buffer_stats_by_table                | object_schema                  |
| innodb_buffer_stats_by_table                | object_name                    |
| innodb_buffer_stats_by_table                | allocated                      |
| innodb_buffer_stats_by_table                | data                           |
| x$innodb_buffer_stats_by_table              | object_schema                  |
| x$innodb_buffer_stats_by_table              | object_name                    |
| x$schema_flattened_keys                     | index_columns                  |
| schema_redundant_indexes                    | redundant_index_columns        |
| schema_redundant_indexes                    | dominant_index_columns         |
| latest_file_io                              | latency                        |
| latest_file_io                              | requested                      |
| io_by_thread_by_latency                     | total_latency                  |
| io_by_thread_by_latency                     | min_latency                    |
| io_by_thread_by_latency                     | avg_latency                    |
| io_by_thread_by_latency                     | max_latency                    |
| ...                                         | ...                            |
| DemoTable                                   | CustomerAllProductPrice        |
| DemoTable                                   | Title                          |
| textdemo                                    | name                           |
| textdemo                                    | sentence                       |
| usercommentsview                            | UserComments                   |
| help_topic                                  | description                    |
| help_topic                                  | example                        |
| help_topic                                  | url                            |
| slave_relay_log_info                        | Relay_log_name                 |
| slave_relay_log_info                        | Master_log_name                |
| slave_master_info                           | Master_log_name                |
| slave_master_info                           | User_name                      |
| slave_master_info                           | User_password                  |
| slave_master_info                           | Ssl_ca                         |
| slave_master_info                           | Ssl_capath                     |
| slave_master_info                           | Ssl_cert                       |
| slave_master_info                           | Ssl_cipher                     |
| slave_master_info                           | Ssl_key                        |
| slave_master_info                           | Bind                           |
| slave_master_info                           | Ignored_server_ids             |
| slave_master_info                           | Uuid                           |
| slave_master_info                           | Ssl_crl                        |
| slave_master_info                           | Ssl_crlpath                    |
| slave_master_info                           | Tls_version                    |
| slave_master_info                           | Public_key_path                |
| posts                                       | post_content                   |
| demotable34                                 | ProgramExtension1              |
| demotable34                                 | ProgramExtension2              |
| demotable34                                 | ImageExtension                 |
| demotable95                                 | Information                    |
| demotabel101                                | URL                            |
| demotable160                                | FileName                       |
| demotable164                                | Header                         |
| demotable165                                | Value                          |
+---------------------------------------------+--------------------------------+
240 rows in set (0.28 sec)

ব্যাখ্যা: এই কোয়েরিটি বর্তমান MySQL ইনস্ট্যান্সের সব ডেটাবেস-এ থাকা TEXT টাইপের কলামগুলো দেখিয়েছে। সিস্টেম টেবিল (INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, mysql) ও ইউজার টেবিল (DemoTable, textdemo, posts, etc.) সবই অন্তর্ভুক্ত।

নির্দিষ্ট ডেটাবেসের জন্য ফিল্টার

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'text' 
  AND TABLE_SCHEMA = 'your_database_name';

TEXT টাইপের ভেরিয়েন্টস

সকল TEXT ভেরিয়েন্ট খুঁজতে

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE IN ('tinytext', 'text', 'mediumtext', 'longtext')
  AND TABLE_SCHEMA = 'your_database_name';

নোট: MySQL 8.0+ এ JSON টাইপও আলাদা, কিন্তু এটি TEXT-ভিত্তিক নয়।