blocks|key|218042|text|使用SQL+Server：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218043|SELECT+DATA_TYPE+
FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+
+++++TABLE_NAME+=+'yourTableName'+AND+
+++++COLUMN_NAME+=+'yourColumnName'|code-block|syntax|javascript|218044|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Using SQL Server:

<pre><code>SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
 TABLE_NAME = 'yourTableName' AND 
 COLUMN_NAME = 'yourColumnName'
</code></pre>

blocks|key|218072|text|在TSQL中最简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218073|SELECT+COLUMN_NAME,+DATA_TYPE+
FROM+INFORMATION_SCHEMA.COLUMNS+
WHERE+TABLE_NAME+=+'yourTableName'|code-block|syntax|javascript|218074|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The easiest way in TSQL is:

<pre><code>SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'yourTableName'
</code></pre>

blocks|key|436408|text|对于SQL+Server，此系统存储过程将返回所有表信息，包括列数据类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436409|exec+sp_help+YOURTABLENAME|code-block|syntax|javascript|436410|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For SQL Server, this system stored procedure will return all table information, including column datatypes:

<pre><code>exec sp_help YOURTABLENAME
</code></pre>

blocks|key|436028|text|在TSQL/MSSQL中，它看起来像：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436029|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+=+''|code-block|syntax|javascript|436030|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In TSQL/MSSQL it looks like:

<pre><code>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 = ''
</code></pre>

blocks|key|436051|text|如果您使用的是MySQL，您可以尝试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436052|SHOW+COLUMNS+FROM+`tbl_name`;|code-block|syntax|javascript|436053|SHOW+COLUMNS+on+dev.mysql.com|offset|length|436054|否则，您应该能够|436055|DESCRIBE+`tbl_name`;|436056|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/show-columns.html^0|0|0|0|T|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

If you're using MySQL you could try

<pre><code>SHOW COLUMNS FROM `tbl_name`;
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.0/en/show-columns.html">SHOW COLUMNS on dev.mysql.com</a>

Otherwise you should be able to do

<pre><code>DESCRIBE `tbl_name`;
</code></pre>

blocks|key|436273|text|在oracle+SQL中，您将执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436274|SELECT
++++DATA_TYPE
FROM
++++all_tab_columns+
WHERE
++++table_name+=+'TABLE+NAME'+--+in+uppercase
AND+column_name+=+'COLUMN+NAME'+--+in+uppercase|code-block|syntax|javascript|436275|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

in oracle SQL you would do this:

<pre><code>SELECT
 DATA_TYPE
FROM
 all_tab_columns 
WHERE
 table_name = 'TABLE NAME' -- in uppercase
AND column_name = 'COLUMN NAME' -- in uppercase
</code></pre>

blocks|key|218577|text|要基于上面的答案进行构建，以声明列所需的相同格式获取列数据类型通常很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218578|例如，varchar(50)、varchar(max)、decimal(p,+s)。|offset|length|style|CODE|218579|这允许您这样做：|218580|SELECT+
++[Name]+++++++++=+c.[name]
,+[Type]+++++++++=+
++++CASE+
++++++WHEN+tp.[name]+IN+('varchar',+'char')+THEN+tp.[name]+%2B+'('+%2B+IIF(c.max_length+=+-1,+'max',+CAST(c.max_length+AS+VARCHAR(25)))+%2B+')'+
++++++WHEN+tp.[name]+IN+('nvarchar','nchar')+THEN+tp.[name]+%2B+'('+%2B+IIF(c.max_length+=+-1,+'max',+CAST(c.max_length+/+2+AS+VARCHAR(25)))%2B+')'++++++
++++++WHEN+tp.[name]+IN+('decimal',+'numeric')+THEN+tp.[name]+%2B+'('+%2B+CAST(c.[precision]+AS+VARCHAR(25))+%2B+',+'+%2B+CAST(c.[scale]+AS+VARCHAR(25))+%2B+')'
++++++WHEN+tp.[name]+IN+('datetime2')+THEN+tp.[name]+%2B+'('+%2B+CAST(c.[scale]+AS+VARCHAR(25))+%2B+')'
++++++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'|code-block|syntax|javascript|218581|entityMap^0|0|3|B|F|C|S|D|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]|$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|P|$]]

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, <code>varchar(50)</code>, <code>varchar(max)</code>, <code>decimal(p, s)</code>. 

This allows you to do that:

<pre><code>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'
</code></pre>

blocks|key|218344|text|另一种使用MS+SQL的变体：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218345|SELECT+TYPE_NAME(system_type_id)+
FROM+sys.columns+
WHERE+name+=+'column_name'
AND+[object_id]+=+OBJECT_ID('[dbo].[table_name]');|code-block|syntax|javascript|218346|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Another variation using MS SQL:

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

blocks|key|218289|text|使用TSQL/MSSQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218290|此查询将得到:表名、列名、数据类型、数据类型长度和允许的空值|218291|SELECT+TABLE_NAME,COLUMN_NAME,+DATA_TYPE,+CHARACTER_MAXIMUM_LENGTH,+IS_NULLABLE
FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+TABLE_NAME+=+'your_table_name'|code-block|syntax|javascript|218292|唯一需要更改的是your_table_name。|218293|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Using TSQL/MSSQL

This query will get you: table name, column name, data type, data type length, and allowable nulls

<pre><code>SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name'
</code></pre>

The only thing that needs to be changed is your_table_name.

blocks|key|436893|text|这也会起作用，因为它只选择列名及其各自的字符类型|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436894|SELECT+COLUMN_NAME+,DATA_TYPE+
FROM+INFORMATION_SCHEMA.COLUMNS+
WHERE+TABLE_NAME+=+'Items';+++|code-block|syntax|javascript|436895|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This also works as it selects just the column names and their respective character type
<pre><code>SELECT COLUMN_NAME ,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Items'; 
</code></pre>

blocks|key|218476|text|要检索实际声明的数据类型，例如在动态SQL中用于更改列，可以使用如下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218477|SELECT
++++TABLE_NAME,+
++++COLUMN_NAME,
++++DATA_TYPE
++++++++%2B+CASE+WHEN+DATA_TYPE+IN+('char','nchar','varchar','nvarchar','binary','varbinary')
++++++++++++++++++++AND+CHARACTER_MAXIMUM_LENGTH+>+0+THEN
+++++++++++++++++COALESCE('('%2BCONVERT(varchar,CHARACTER_MAXIMUM_LENGTH)%2B')','')
++++++++++++ELSE+''+END
++++++++%2B+CASE+WHEN+DATA_TYPE+IN+('decimal','numeric')+THEN
++++++++++++++++COALESCE('('%2BCONVERT(varchar,NUMERIC_PRECISION)%2B','%2BCONVERT(varchar,NUMERIC_SCALE)%2B')','')
++++++++++++ELSE+''+END
++++++++AS+Declaration_Type,
++++CASE+WHEN+IS_NULLABLE='NO'+THEN+'NOT+'+ELSE+''+END+%2B+'NULL'+AS+Nullable
FROM+INFORMATION_SCHEMA.COLUMNS
ORDER+BY+1,2|code-block|syntax|javascript|218478|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To retrieve the actual declared data types, for example for use in dynamic SQL to ALTER COLUMNs, something like this can be used:

<pre><code>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
</code></pre>

blocks|key|436123|text|USE+[YourDatabaseName]
GO

SELECT+column_name+'Column+Name',
data_type+'Data+Type'
FROM+information_schema.columns
WHERE+table_name+=+'YourTableName'
GO|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|436124|这将返回值列名称，显示列的名称以及这些列的数据类型(int、varchars等)。|unstyled|436125|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>USE [YourDatabaseName]
GO

SELECT column_name 'Column Name',
data_type 'Data Type'
FROM information_schema.columns
WHERE table_name = 'YourTableName'
GO
</code></pre>

This will return the values Column Name, showing you the names of the columns, and the Data Types of those columns (ints, varchars, etc).

blocks|key|436509|text|使用TSQL/MSSQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436510|您可以使用INTO关键字。|offset|length|style|CODE|436511|将SELECT的结果转换为实表|436512|示例：select+....+INTO+real_table_name|436513|之后|436514|sp_help+real_table_name|code-block|syntax|javascript|436515|entityMap^0|0|5|4|0|1|6|0|3|W|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|14|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|15|8|@]|9|@]|A|$Q|R]]|$1|S|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|T|$]]

Using TSQL/MSSQL

You can use <code>INTO</code> keyword.

The result of <code>SELECT</code> into a real TABLE

Example: <code>select .... INTO real_table_name</code>

After

<pre><code>sp_help real_table_name
</code></pre>

blocks|key|218626|text|使用此查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218627|SELECT+*+FROM+INFORMATION_SCHEMA.COLUMNS
WHERE+TABLE_NAME+=+'TABLE_NAME'
ORDER+BY+ORDINAL_POSITION|code-block|syntax|javascript|218628|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

use this query
<pre><code>SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY ORDINAL_POSITION
</code></pre>

blocks|key|436908|text|从SQL+Server+2012开始：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436909|SELECT+*+FROM+sys.dm_exec_describe_first_result_set(+N'SELECT+*+FROM+[my].[Table]',+NULL,+0+);|code-block|syntax|javascript|436910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

From SQL Server 2012 on:
<pre><code>SELECT * FROM sys.dm_exec_describe_first_result_set( N'SELECT * FROM [my].[Table]', NULL, 0 );
</code></pre>

blocks|key|436337|text|在我的例子中，我需要获取动态SQL的数据类型(颤栗！)不管怎样，下面是我创建的一个函数，它返回完整的数据类型。例如，它将返回DECIMAL(18，4)，而不是返回'decimal‘：dbo.GetLiteralDataType|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|436338|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/31706936/1819403^0|2J|M|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

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): <a href="https://stackoverflow.com/a/31706936/1819403">dbo.GetLiteralDataType</a>

blocks|key|436530|text|使用此查询获取架构、表、列、类型、max_length、is_nullable|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436531|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]|code-block|syntax|javascript|436532|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use this query to get Schema, Table, Column,Type, max_length, is_nullable

<pre><code>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]
</code></pre>

blocks|key|218390|text|SHOW+COLUMNS+FROM+//table_name//+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|218391|它将为您提供有关表中所有列的信息。|unstyled|218392|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SHOW COLUMNS FROM //table_name// ;
</code></pre>

It will give you information about all the columns from the table .

blocks|key|218518|text|MS+SQL的另一个选择是将此处的select查询替换为您想要其类型的查询：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|218519|declare+@sql+varchar(4000);

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

select+*+from+master.sys.dm_exec_describe_first_result_set+(@sql,+Null,+0);|code-block|syntax|javascript|218520|entityMap^0|H|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Another option for MS SQL is to replace the <code>select</code> query here with the query you want the types for:

<pre><code>declare @sql varchar(4000);

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

select * from master.sys.dm_exec_describe_first_result_set (@sql, Null, 0);
</code></pre>

blocks|key|436572|text|对于Spark+SQL|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|436573|DESCRIBE+[db_name.]table_name+column_name|code-block|syntax|javascript|436574|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.databricks.com/spark/latest/spark-sql/language-manual/describe-table.html^0|2|9|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

For <a href="https://docs.databricks.com/spark/latest/spark-sql/language-manual/describe-table.html" rel="nofollow noreferrer">Spark SQL</a>:

<pre><code>DESCRIBE [db_name.]table_name column_name
</code></pre>

blocks|key|218368|text|对于this+answer中所示的Apache+Derby|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|218369|select+columndatatype+from+sys.syscolumns
++where+referenceid+=+(
++++select+tableid+from+sys.systables
++++where+tablename+=+'YOUR_TABEL_NAME'
++++and+columnname=+'YOUR_COLUMN_NAME')|code-block|syntax|javascript|218370|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/41774881|1|https://db.apache.org/derby/^0|2|B|0|H|C|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

For <a href="https://db.apache.org/derby/" rel="nofollow noreferrer">Apache Derby</a> as shown in <a href="https://stackoverflow.com/a/41774881">this answer</a>:

<pre><code>select columndatatype from sys.syscolumns
 where referenceid = (
 select tableid from sys.systables
 where tablename = 'YOUR_TABEL_NAME'
 and columnname= 'YOUR_COLUMN_NAME')
</code></pre>

blocks|key|436611|text|在vb60中，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436612|Public+Cn+As+ADODB.Connection
'open+connection
Dim+Rs+As+ADODB.Recordset
+Set+Rs+=+Cn.OpenSchema(adSchemaColumns,+Array(Empty,+Empty,+UCase("Table"),+UCase("field")))|code-block|syntax|javascript|436613|‘和示例(valRs是我对rs.fields(“CHARACTER_MAXIMUM_LENGTH”).value的函数)：|436614|+RT_Charactar_Maximum_Length+=+(ValRS(Rs,+"CHARACTER_MAXIMUM_LENGTH"))
++++++++rt_Tipo+=+(ValRS(Rs,+"DATA_TYPE"))|436615|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In vb60 you can do this:

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

'and sample (valRs is my function for rs.fields("CHARACTER_MAXIMUM_LENGTH").value):

<pre><code> RT_Charactar_Maximum_Length = (ValRS(Rs, "CHARACTER_MAXIMUM_LENGTH"))
 rt_Tipo = (ValRS(Rs, "DATA_TYPE"))
</code></pre>

blocks|key|436708|text|由于有些人要求数据类型的精确度，所以我想分享我为此目的创建的脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|436709|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'|code-block|syntax|javascript|436710|它不是完美的，但在大多数情况下都是有效的。|436711|使用Sql-Server|offset|length|style|CODE|436712|entityMap^0|0|0|0|2|A|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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.

<pre><code>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'
</code></pre>

It's not perfect but it works in most cases.

Using <code>Sql-Server</code>

blocks|key|218674|text|我发现这对查询很有用，特别是当它们使用聚合或多个表(SQL+Server)时。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218675|DECLARE+@query+nvarchar(max)+=+'select+*+from+yourtable';
EXEC+sp_describe_first_result_set+@query,+null,+0;+|code-block|syntax|javascript|218676|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I find this useful for queries, especially if they use aggregates or several tables (SQL Server)
<pre><code>DECLARE @query nvarchar(max) = 'select * from yourtable';
EXEC sp_describe_first_result_set @query, null, 0; 
</code></pre>

blocks|key|218768|text|如果有人觉得这个有用的话。在SQL+Server中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|218769|++++++sp_columns+'yourtablename'|code-block|syntax|javascript|218770|这将提供所有列的详细信息。它提供column_name、data_type、type_name、precision、length、iss_nullable等。|218771|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Just if someone finds this useful. In SQL Server:
<pre><code> sp_columns 'yourtablename'
</code></pre>
This will give details of all the columns. It gives you column_name, data_type, type_name, precision, length, iss_nullable, etc.

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

问用于获取列类型的SQL语句
EN

回答 25

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于获取列类型的SQL语句EN

回答 25

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于获取列类型的SQL语句
EN