6

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'
mahdi pishguy
  • 994
  • 1
  • 14
  • 43
  • I'm finding it difficult to understand what you want here. The information_schema.columns column_type field describes the column attributes including the maximum length that can be held in that column. But if you want to know the actual maximum lengths then you have to look at the data in the table. Do you want all values from the table including the length of every column together with the description of the column from information_schema.columns? – P.Salmon Oct 21 '16 at 11:43
  • @P.Salmon yes sir, problem resolved by this post http://stackoverflow.com/a/40175051/6599627 – mahdi pishguy Oct 21 '16 at 14:18

3 Answers3

5

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

P James
  • 101
  • 1
  • 5
  • Sorry you can't have spaces in the as "..." This should work: SELECT COLUMN_NAME, LEN(COLUMN_NAME) as Column_Length, DATA_TYPE, LEN(DATA_TYPE) as Data_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tableName' – P James Oct 21 '16 at 11:15
  • unfortunately i get this error: `#1305 - FUNCTION information_schema.LEN does not exist` – mahdi pishguy Oct 21 '16 at 11:26
  • Sorry, LEN() Is an SQL function, mysql its LENGTH(). `SELECT COLUMN_NAME, LENGTH(COLUMN_NAME) as Column_Length, DATA_TYPE, LENGTH(DATA_TYPE) as Data_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tableName' ` – P James Oct 21 '16 at 11:30
  • returned value is not correct, because `channelName` length is `varchar(30)`, by your command i get `11` on `Column_Length` – mahdi pishguy Oct 21 '16 at 11:33
  • Ok so I searched around and found this `SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'Database' AND TABLE_NAME = 'Table' AND COLUMN_NAME = 'Field'` From here: http://stackoverflow.com/questions/7927512/mysql-determine-longest-varchar-length – P James Oct 21 '16 at 12:44
2

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.

0

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!

javiergarval
  • 348
  • 3
  • 14