CODEWITHSUNDEEP
mysqlsqltypessqldatatypes
Kenny
Kenny

Reputation: 43

SQL Select including Data type and Data values

How could I select data from a table and include the data type with the values? For example, I have table called EMPLOYEE with fname, lastname, phone, etc. If I do:

SELECT * FROM EMPLOYEE

I get all the columns. If I do: 

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEE'
ORDER BY ORDINAL_POSITION

I get all the column names and their data types.

I need to combine these two so I have the values displayed along with the data type.

I'm new to SQL but I have searched on the web some and tried various JOIN and UNION statements, but I could just be entering them wrong.

Upvotes: 4

Views: 13339

Answers (1)

Levente Pánczél
Levente Pánczél

Reputation: 1923

You could SELECT EMPLOYEE.COL1, INFO.DATA_TYPE FROM EMPLOYEE, INFORMATION_SCHEMA.COLUMNS INFO WHERE INFO.TABLE_NAME='EMPLOYEE' AND COLUMN_NAME='COL1' But to select more fields, you'd have to add another instance of INFORMATION_SCHEMA.COLUMNS again with a different alias.

But you should not do this for many reasons.

Technically: CROSS JOIN (the tables in FROM listed simply with a comma) is much strain on the database server. FROM T1, T2 pairs up all rows of T1 with all rows of T2 and examines the result rows. If T1 has n rows and T2 has m then the result has n*m rows.

Logically 1: You should not need this information to be returned. When you enter a query (a SELECT) the the returned schema is known; the query determines what datatypes the result columns are. Logically 2: Since every row has the same datatypes in the columns, you do not need the type information to be returned in every row. E.g. your example returning data of 1000 employees would unnecessarily transfer on every line that the AGE field is INTEGER, the NAME is VARCHAR and so on...

If you somehow would not know the schema of the result (e.g. because of generated queries or the like) then the above solution would not help you.

Upvotes: 2

Related Questions