SQL语句获取列类型

在TSQL/MSSQL中，它看起来像:

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 = ''

如果你正在使用MySQL，你可以试试

SHOW COLUMNS FROM `tbl_name`;

SHOW COLUMNS on dev.mysql.com

否则你应该能做到

DESCRIBE `tbl_name`;

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等)。

使用SQL Server:

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

在TSQL中最简单的方法是:

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

对于IBM DB2:

SELECT TYPENAME FROM SYSCAT.COLUMNS WHERE TABSCHEMA='your_schema_name' AND TABNAME='your_table_name' AND COLNAME='your_column_name'

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

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

在我的情况下，我需要获得动态SQL的数据类型(Shudder!)无论如何，这里是一个函数，我创建返回完整的数据类型。例如，它将返回decimal (18,4): dbo。GetLiteralDataType而不是返回'decimal'

对于SQL Server，这个系统存储过程将返回所有表信息，包括列数据类型:

exec sp_help YOURTABLENAME

使用TSQL /该软件

该查询将为您提供:表名、列名、数据类型、数据类型长度和允许的空值

SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'

唯一需要更改的是your_table_name。

使用TSQL /该软件

你可以使用INTO关键字。

SELECT的结果转换为一个真正的TABLE

例如:select .... INTO real_table_name

后

sp_help real_table_name

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

使用MS SQL的另一个变体:

SELECT TYPE_NAME(system_type_id)
FROM sys.columns
WHERE name = 'column_name'
AND [object_id] = OBJECT_ID('[dbo].[table_name]');

火花SQL:

DESCRIBE [db_name.]table_name column_name

对于Apache Derby，如这个答案所示:

select columndatatype from sys.syscolumns
where referenceid = (
select tableid from sys.systables
where tablename = 'YOUR_TABEL_NAME'
and columnname= 'YOUR_COLUMN_NAME')

SHOW COLUMNS FROM //table_name// ;

它将为您提供关于表中所有列的信息。

在vb60中，你可以这样做:

Public Cn As ADODB.Connection
'open connection
Dim Rs As ADODB.Recordset
Set Rs = Cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, UCase("Table"), UCase("field")))

'和sample (valRs是rs.fields("CHARACTER_MAXIMUM_LENGTH").value的函数):

 RT_Charactar_Maximum_Length = (ValRS(Rs, "CHARACTER_MAXIMUM_LENGTH"))
rt_Tipo = (ValRS(Rs, "DATA_TYPE"))

要检索实际声明的数据类型，例如在动态SQL To ALTER COLUMNs中使用，可以使用这样的方法:

SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','binary','varbinary')
AND CHARACTER_MAXIMUM_LENGTH > 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

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

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

MS SQL的另一个选项是将这里的select查询替换为您想要类型的查询:

declare @sql varchar(4000);


set @sql = 'select ''hi'' as greeting';


select * from master.sys.dm_exec_describe_first_result_set (@sql, Null, 0);

要在上面的答案的基础上进行构建，获得列数据类型的格式与声明列的格式相同通常是很有用的。

例如，varchar(50)， varchar(max)， decimal(p, s)。

这允许你这样做:

SELECT
[Name]         = c.[name]
, [Type]         =
CASE
WHEN tp.[name] IN ('varchar', 'char', 'varbinary') 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
, [IsNullable]   = c.is_nullable
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'

使用这个查询

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY ORDINAL_POSITION

我发现这对查询很有用，特别是如果他们使用聚合或几个表(SQL Server)

DECLARE @query nvarchar(max) = 'select * from yourtable';
EXEC sp_describe_first_result_set @query, null, 0;

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

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 );

如果有人觉得这个有用的话。在SQL Server中:

      sp_columns 'yourtablename'

这将给出所有列的详细信息。它提供了column_name、data_type、type_name、precision、length、iss_nullable等。

下面的SQL脚本在我的结束完美地工作。

SELECT column_name AS 'Column Name',data_type AS 'Data Type'
FROM information_schema.columns
WHERE table_name = '[Table Name]'

我认为在Microsoft SQL Server Management Studio中最简单和最有用的方法是:

EXEC [ServerName].[DatabaseName].dbo.sp_help 'TableName'