SQL Statement to Get Column Type

SQL statement to get column type

Using SQL Server:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_SCHEMA = 'yourSchemaName' AND
     TABLE_NAME   = 'yourTableName' AND 
     COLUMN_NAME  = 'yourColumnName'

SQL Server query to get data type for all columns in table?

SELECT c.name,
       c.max_length,
       c.precision,
       c.scale,
       c.is_nullable,
       t.name
  FROM sys.columns c
  JOIN sys.types   t
    ON c.user_type_id = t.user_type_id
 WHERE c.object_id    = Object_id('dbo.testtable')

How do I return the SQL data types from my query?

select * from information_schema.columns

could get you started.

Check for column type if statement

I've managed to do what I wanted. Maybe someone will benefit from this:

IF 'Int' in (SELECT DATA_TYPE 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
    TABLE_NAME = 'TempTable' AND 
    COLUMN_NAME = 'Id')
    BEGIN
        ALTER TABLE TempTable ADD PRIMARY KEY (Id)
    END
ELSE
    BEGIN
        ALTER TABLE TempTable ADD Idd int IDENTITY (1,1)
        ALTER TABLE TempTable ADD PRIMARY KEY (Idd)
    END
GO

Basicaly it checks if column is of type INT if yes, it adds Primary Key on Id column. If it isn't it creates new column Idd and makes it int Primary Key.

Pretty specific problem, maybe someone will make use of my torment.

EDIT: Something is wrong with checking whether Int is in selected type.

SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Just replace YourTableName with your actual table name - works for SQL Server 2005 and up.

In case you are using schemas, replace YourTableName by YourSchemaName.YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name.

How to display data type of all columns in a sql query

Just use

Select COLUMN_NAME , DATA_TYPE 
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

BTW if you use the in clause, then select only a single column in the subquery and not all

where COLUMN_NAME IN (Select col from table)

and not

where COLUMN_NAME IN (Select * from table)

Get column data type from table and some condition

You can directly use INFORMATION_SCHEMA.COLUMNS system view as

SELECT TABLE_NAME,
       CASE DATA_TYPE WHEN 'INT' THEN COLUMN_NAME
                      --WHEN 'VARCHAR' I don't know what you mean by "in horizontal line alien"
       END
FROM INFORMATION_SCHEMA.COLUMNS;
--WHERE TABLE_NAME = 'TableName'

Or

SELECT TABLE_NAME,
       STRING_AGG(CASE WHEN DATA_TYPE = 'VARCHAR'
                       THEN QUOTENAME(COLUMN_NAME, '''')
                       ELSE COLUMN_NAME
                  END, ',') Columns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME;
--WHERE TABLE_NAME = 'TableName'

Demo

Or

SELECT TABLE_NAME,
       STUFF(
         (SELECT ',' + CASE WHEN DATA_TYPE = 'VARCHAR' 
                            THEN QUOTENAME(COLUMN_NAME, '''')
                            ELSE COLUMN_NAME
                       END
          FROM INFORMATION_SCHEMA.COLUMNS T2
          WHERE T1.TABLE_NAME = T2.TABLE_NAME
          FOR XML PATH('')

         ), 1, 1, ''
       ) Columns
FROM INFORMATION_SCHEMA.COLUMNS T1
GROUP BY TABLE_NAME;

2nd Demo

---

UPDATE:

Since you're trying to return GETDATE() value when the data type is DATETIME then

SELECT TABLE_NAME,
       STUFF(
         (SELECT ',' + CASE DATA_TYPE WHEN 'VARCHAR' 
                                          THEN QUOTENAME(COLUMN_NAME, '''')
                                      WHEN 'DATETIME'
                                          THEN 'GETDATE()'
                            ELSE COLUMN_NAME
                       END
          FROM INFORMATION_SCHEMA.COLUMNS T2
          WHERE T1.TABLE_NAME = T2.TABLE_NAME
          FOR XML PATH('')

         ), 1, 1, ''
       ) Columns
FROM INFORMATION_SCHEMA.COLUMNS T1
GROUP BY TABLE_NAME;

SQL Server/TSQL - Get column literal data type (with length)

Below is an example T-SQL script that will return the full column definition. You can remove the properties you don't need if you don't want the full column definition.

DECLARE @SourceTableName nvarchar(261) = N'Person.Address';
SELECT 
    CASE 
        WHEN c.is_computed = 1 
            THEN QUOTENAME(c.name) + N' AS (' + cc.definition + N')'
        ELSE
            QUOTENAME(c.name) 
            + ' ' + TYPE_NAME(c.user_type_id)
            + CASE 
                --types without length, precision, or scale specifiecation
                WHEN TYPE_NAME(c.user_type_id) IN (N'int',N'bigint',N'smallint',N'tinyint',N'money',N'smallmoney',N'real',N'datetime',N'smalldatetime',N'bit',N'image',N'text',N'uniqueidentifier',N'date',N'ntext',N'sql_variant',N'hierarchyid','geography',N'timestamp',N'xml') 
                    THEN N''
                --types with precision and scale specification
                WHEN TYPE_NAME(c.user_type_id) in (N'decimal',N'numeric') 
                    THEN N'(' + CAST(c.precision AS varchar(5)) + N',' + CAST(c.scale AS varchar(5)) + N')'
                --types with scale specification only
                WHEN TYPE_NAME(c.user_type_id) in (N'time',N'datetime2',N'datetimeoffset') 
                    THEN N'(' + CAST(c.scale AS varchar(5)) + N')'
                --float default precision is 53 - add precision when column has a different precision value
                WHEN TYPE_NAME(c.user_type_id) in (N'float')
                    THEN CASE WHEN c.precision = 53 THEN N'' ELSE N'(' + CAST(c.precision AS varchar(5)) + N')' END
                --types with length specifiecation
                ELSE N'(' + CASE c.max_length WHEN -1 THEN N'MAX' ELSE CAST(c.max_length AS nvarchar(20)) END + N')'
        END
        + CASE WHEN c.is_filestream = 1 THEN N' FILESTREAM' ELSE '' END
        + COALESCE(N' COLLATE ' + c.collation_name, N'')
        + CASE WHEN c.is_sparse = 1 THEN N' SPARSE' ELSE N'' END
        + CASE WHEN c.is_identity = 1 THEN N' IDENTITY(' + CAST(IDENT_SEED(@SourceTableName) AS varchar(20)) + ',' + CAST(IDENT_INCR(@SourceTableName) AS varchar(20)) + ')' ELSE '' END
        + CASE WHEN c.is_rowguidcol = 1 THEN N' ROWGUIDCOL' ELSE '' END
        + CASE WHEN c.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END
    END
FROM sys.columns AS c (NOLOCK) 
LEFT JOIN sys.computed_columns AS cc (NOLOCK) ON
    cc.object_id = c.object_id
    AND cc.column_id = c.column_id
WHERE
    c.object_id = OBJECT_ID(@SourceTableName)
ORDER BY c.column_id;

Results, from the AdventureWorks database Person.Address table:

[AddressID] int IDENTITY(1,1) NOT NULL
[AddressLine1] nvarchar(120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
[AddressLine2] nvarchar(120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[City] nvarchar(60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
[StateProvinceID] int NOT NULL
[PostalCode] nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
[SpatialLocation] geography NULL
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
[ModifiedDate] datetime NOT NULL

In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?

Columns for view:

SELECT * FROM sys.columns c where c.object_id = OBJECT_ID('<schema>.<view name>')

Columns for table valued function:

SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS rc WHERE rc.TABLE_NAME = '<udf name>'

Columns for stored procedure

For SQL server 2012 and later:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID('<shcema>.<sp name>'), 
  NULL
);

Taken from Retrieve column names and types of a stored procedure?. Read answers there for possible ways to do this (for example with pre 2012).

---

Related Topics