Как определить тип данных в sql

Как получить тип данных столбцов таблицы MySQL?

Думал, что я мог бы использовать структуру MYSQLFIELD , но это были перечисленные типы полей.

Затем я попытался с mysql_real_query()

Ошибка, которую я получаю, это query was empty

Как получить тип данных столбца?

10 ответов

Вы можете использовать таблицу information_schema:

В приведенном ниже запросе приводится список сведений о каждом поле, включая тип поля MySQL. Вот пример:

Большинство ответов — это дубликаты, может быть полезно сгруппировать их. В принципе предлагается два простых варианта.

Первый вариант

Первый вариант имеет 4 разных псевдонима, некоторые из которых довольно короткие:

(NB: в качестве альтернативы db_name.table_name можно использовать второй FROM : db_name FROM table_name ).

Это дает что-то вроде:

Второй вариант

Второй вариант немного длиннее:

Он также менее разговорчив:

У этого есть преимущество разрешить выбор для столбца, хотя, используя AND COLUMN_NAME = ‘column_name’ (или like ).

Как узнать тип данных в столбце sql

Аватар пользователя Roman Ashikov

Приведу запрос с помощью которого можно имена полей и тип данных в таблице products:

Аватар пользователя Artem S.

3 Ways to Get a Column’s Data Type in SQL Server (T-SQL)

GUIs like SSMS or Azure Data Studio make it easy to see a column’s data type. Usually it’s a simple matter of navigating to the column in the object explorer and you can see the data type right next to the column.

But if you’re using T-SQL, you’ll need to run a query.

The information_schema.columns View

The information_schema.columns view is a good option if you simply want the data type and no more:

Replace Products and ProductName with the name of your table and column respectively.

OK, I returned a bit more than just the data type here. But you can omit the other columns if required. Or you can add more. For example, there are columns that contain the column’s precision in case you’re looking at a numeric or datetime column.

You can return all columns like this:

The sys.columns View

The sys.columns view is another option. We can join this with the sys.tables view to get a specific column from a specific table:

Again, include more or less columns as required.

In this example, I used the TYPE_NAME() function to return the name of the data type, based on its ID. This saved me from having to do a join on the sys.types table.

The sp_help Stored Procedure

The sp_help stored procedure can be useful if you want to return more information about the table.

This stored procedure returns information about a database object (any object listed in the sys.sysobjects compatibility view), a user-defined data type, or a data type:

This returns a lot of output, so I won’t list it all here.

Just replace Products with the name of the table or other object you want to get information about.

Get data type of field in select statement in ORACLE

Can I get data types of each column I selected instead of the values, using a select statement?

and result should be like this

or it can be in row like this, it isn’t important:

8 Answers 8

I found a not-very-intuitive way to do this by using DUMP()

It will return something like:

‘Typ=1 Len=2: 0,48’ for each column.

Type=1 means VARCHAR2/NVARCHAR2
Type=2 means NUMBER/FLOAT
Type=12 means DATE , etc.

You can refer to this oracle doc for information Datatype Code
or this for a simple mapping Oracle Type Code Mappings

You can query the all_tab_columns view in the database.

I usually create a view and use the DESC command:

Then, the DESC command will show the type of each field.

I came into the same situation. As a workaround, I just created a view (If you have privileges) and described it and dropped it later. 🙂

If you don’t have privileges to create a view in Oracle, a «hack» around it to use MS Access 🙁

In MS Access, create a pass through query with your sql (but add where clause to just select 1 record), create a select query from the view (very important), selecting all *, then create a make table from the select query. When this runs it will create a table with one record, all the data types should «match» oracle. i.e. Passthrough —> Select —> MakeTable —> Table

I am sure there are other better ways, but if you have limited tools and privileges this will work.

Also, if you have Toad for Oracle, you can highlight the statement and press CTRL + F9 and you’ll get a nice view of column and their datatypes.

Field data type data is available from client code in the ODP.Net. Expect Oracle other libraries must support schema information too. It is straight forward in C# Script you just need a connection string and the SELECT statement. Ask for the Schema Data Only. This solution requires no extras CREATE VIEW rights needed with my testing so far. Resolves type on select expressions. Con is does add extra round trips to data base.

The example is using c# 10 ,may need to downgrade the syntax. The Constants.ContainerConnectionString is a connection string and the Constants.DvcrSyntax can be any select statement.

The Overflow Blog

Linked

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.