SQL statement to get column type

Question

Is there a SQL statement that can return the type of a column in a table

User · Answer

The easiest way in TSQL is   SELECT COLUMN NAME  DATA TYPE  FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME    yourTableName

User · Answer

For SQL Server  this system stored procedure will return all table information  including column datatypes   exec sp help YOURTABLENAME

User · Answer

USE  YourDatabaseName  GO  SELECT column name  Column Name   data type  Data Type  FROM information schema columns WHERE table name    YourTableName  GO   This will return the values Column Name  showing you the names of the columns  and the Data Types of those columns  ints  varchars  etc

User · Answer

use this query SELECT   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    TABLE NAME  ORDER BY ORDINAL POSITION

User · Answer

This also works as it selects just the column names and their respective character type SELECT COLUMN NAME  DATA TYPE  FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME    Items

User · Answer

If you re using MySQL you could try  SHOW COLUMNS FROM  tbl name     SHOW COLUMNS on dev mysql com  Otherwise you should be able to do  DESCRIBE  tbl name

User · Answer

In vb60 you can do this   Public Cn As ADODB Connection  open connection Dim Rs As ADODB Recordset  Set Rs   Cn OpenSchema adSchemaColumns  Array Empty  Empty  UCase  Table    UCase  field        and sample  valRs is my function for rs fields  CHARACTER MAXIMUM LENGTH   value     RT Charactar Maximum Length    ValRS Rs   CHARACTER MAXIMUM LENGTH            rt Tipo    ValRS Rs   DATA TYPE

User · Answer

in oracle SQL you would do this   SELECT     DATA TYPE FROM     all tab columns  WHERE     table name    TABLE NAME  -- in uppercase AND column name    COLUMN NAME  -- in uppercase

User · Answer

Using TSQL MSSQL  This query will get you  table name  column name  data type  data type length  and allowable nulls  SELECT TABLE NAME COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH  IS NULLABLE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    your table name    The only thing that needs to be changed is your table name

User · Answer

In my case I needed to get the data type for Dynamic SQL  Shudder   anyway here is a function that I created that returns the full data type  For example instead of returning  decimal  it would return DECIMAL 18 4   dbo GetLiteralDataType

User · Answer

SHOW COLUMNS FROM   table name       It will give you information about all the columns from the table

User · Answer

I find this useful for queries  especially if they use aggregates or several tables  SQL Server  DECLARE  query nvarchar max     select   from yourtable   EXEC sp describe first result set  query  null  0

User · Answer

To build on the answers above  it s often useful to get the column data type in the same format that you need to declare columns    For example  varchar 50   varchar max   decimal p  s     This allows you to do that   SELECT     Name            c  name     Type                 CASE        WHEN tp  name  IN   varchar    char   THEN tp  name          IIF c max length   -1   max   CAST c max length AS VARCHAR 25                 WHEN tp  name  IN   nvarchar   nchar   THEN tp  name          IIF c max length   -1   max   CAST c max length   2 AS VARCHAR 25                     WHEN tp  name  IN   decimal    numeric   THEN tp  name          CAST c  precision  AS VARCHAR 25            CAST c  scale  AS VARCHAR 25               WHEN tp  name  IN   datetime2   THEN tp  name          CAST c  scale  AS VARCHAR 25               ELSE tp  name      END    RawType         tp  name     MaxLength       c max length    Precision       c  precision     Scale           c scale FROM sys tables t  JOIN sys schemas s ON t schema id   s schema id JOIN sys columns c ON t object id   c object id JOIN sys types tp ON c user type id   tp user type id WHERE s  name     dbo  AND t  name     MyTable

User · Answer

To retrieve the actual declared data types  for example for use in dynamic SQL to ALTER COLUMNs  something like this can be used   SELECT     TABLE NAME       COLUMN NAME      DATA TYPE           CASE WHEN DATA TYPE IN   char   nchar   varchar   nvarchar   binary   varbinary                       AND CHARACTER MAXIMUM LENGTH  gt  0 THEN                  COALESCE     CONVERT varchar CHARACTER MAXIMUM LENGTH                      ELSE    END           CASE WHEN DATA TYPE IN   decimal   numeric   THEN                 COALESCE     CONVERT varchar NUMERIC PRECISION      CONVERT varchar NUMERIC SCALE                      ELSE    END         AS Declaration Type      CASE WHEN IS NULLABLE  NO  THEN  NOT   ELSE    END    NULL  AS Nullable FROM INFORMATION SCHEMA COLUMNS ORDER BY 1 2

User · Answer

For IBM DB2     SELECT TYPENAME FROM SYSCAT COLUMNS WHERE TABSCHEMA  your schema name  AND TABNAME  your table name  AND COLNAME  your column name

User · Answer

Another variation using MS SQL   SELECT TYPE NAME system type id   FROM sys columns  WHERE name    column name  AND  object id    OBJECT ID   dbo   table name

User · Answer

Since some people were asking for the precision as well with the data type  I would like to share my script that I have created for such a purpose   SELECT TABLE NAME As  TableName         COLUMN NAME As  ColumnName         CONCAT DATA TYPE       COALESCE CHARACTER MAXIMUM LENGTH  NUMERIC PRECISION  DATETIME PRECISION       IIF NUMERIC SCALE  lt  gt  0  CONCAT       NUMERIC SCALE             IIF IS NULLABLE    YES      null      not null    As  ColumnType  FROM INFORMATION SCHEMA COLUMNS WHERE --     ORDER BY  TableName    ColumnName    It s not perfect but it works in most cases   Using Sql-Server

User · Answer

Using SQL Server   SELECT DATA TYPE  FROM INFORMATION SCHEMA COLUMNS WHERE       TABLE NAME    yourTableName  AND       COLUMN NAME    yourColumnName

User · Answer

In TSQL MSSQL it looks like   SELECT t name  c name  FROM sys tables t  JOIN sys columns c ON t object id   c object id JOIN sys types y ON y user type id   c user type id WHERE t name

User · Answer

Use this query to get Schema  Table  Column Type  max length  is nullable  SELECT QUOTENAME SCHEMA NAME tb  schema id    AS  Schema       QUOTENAME OBJECT NAME tb  OBJECT ID    AS  Table       C NAME as  Column       T name AS  Type       C max length      C is nullable FROM SYS COLUMNS C INNER JOIN SYS TABLES tb ON tb  object id    C  object id      INNER JOIN SYS TYPES T ON C system type id   T user type id WHERE tb  is ms shipped    0 ORDER BY tb  Name

User · Answer

Another option for MS SQL is to replace the select query here with the query you want the types for   declare  sql varchar 4000    set  sql    select   hi   as greeting    select   from master sys dm exec describe first result set   sql  Null  0

User · Answer

Using TSQL MSSQL  You can use INTO keyword   The result of SELECT into a real TABLE  Example  select      INTO real table name  After  sp help real table name

User · Answer

For Apache Derby as shown in this answer   select columndatatype from sys syscolumns   where referenceid         select tableid from sys systables     where tablename    YOUR TABEL NAME      and columnname   YOUR COLUMN NAME

User · Answer

For Spark SQL   DESCRIBE  db name  table name column name

[sql] SQL statement to get column type

Examples related to sql

Examples related to schema