这同样适用于只选择列名及其各自的字符类型

SELECT COLUMN_NAME ,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Items';   

从SQL Server 2012开始:

SELECT * FROM sys.dm_exec_describe_first_result_set( N'SELECT * FROM [my].[Table]', NULL, 0 );

使用此查询获取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]

在oracle SQL中，你可以这样做:

SELECT
    DATA_TYPE
FROM
    all_tab_columns 
WHERE
    table_name = 'TABLE NAME' -- in uppercase
AND column_name = 'COLUMN NAME' -- in uppercase

USE [YourDatabaseName]
GO

SELECT column_name 'Column Name',
data_type 'Data Type'
FROM information_schema.columns
WHERE table_name = 'YourTableName'
GO

这将返回值Column Name，显示列的名称，以及这些列的数据类型(int, varchars等)。

由于有些人要求数据类型的精度，我想分享我为此目的而创建的脚本。

SELECT TABLE_NAME As 'TableName'
       COLUMN_NAME As 'ColumnName'
       CONCAT(DATA_TYPE, '(', COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, DATETIME_PRECISION, ''), IIF(NUMERIC_SCALE <> 0, CONCAT(', ', NUMERIC_SCALE), ''), ')', IIF(IS_NULLABLE = 'YES', ', null', ', not null')) As 'ColumnType'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE -- ...
ORDER BY 'TableName', 'ColumnName'

这并不完美，但在大多数情况下都有效。

使用sql server