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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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