CODEWITHSUNDEEP
mysql
mahdi pishguy
mahdi pishguy

Reputation: 1034

How can i get mySql column Length/Values

I can get column table information with this command, but that don't return column Length/Values, how can i get that?

SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tableName'

Upvotes: 8

Views: 19246

Answers (3)

P James
P James

Reputation: 141

SQL has a LEN() Function. Which will give you a length of a field. However updated would be the solution:

UPDATED

SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'Database' AND TABLE_NAME = 'Table' AND COLUMN_NAME = 'Field'

Hope that helps

Upvotes: 9

javiergarval
javiergarval

Reputation: 348

The easiest way to do is using the LEN() function as this example:

SELECT CustomerName,LEN(Address) as LengthOfAddress
FROM Customers;

So for your code should be:

SELECT COLUMN_NAME, LEN(COLUMN_NAME) as NAME_LENGTH, 
DATA_TYPE, LEN(DATA_TYPE) as DATA_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tableName';

Here you have more information!

Upvotes: 0

user6077173
user6077173

Reputation:

Please check if COLUMN_TYPE is what you are looking for.

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tableName';

Also, you may refer all the columns in INFORMATION_SCHEMA.COLUMNS table and query data that might be useful to you.

Upvotes: 2

Related Questions