如何从查询中返回 SQL 数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1601727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I return the SQL data types from my query?
提问by JMP
I've a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don't need nor want to understand. The result of this query needs to be stored in a staging table to feed a report.
我有一个 SQL 查询，它查询一个巨大的（例如，数百个视图/表，名称难以阅读，如 CMM-CPP-FAP-ADD），我不需要也不想理解这些数据库。此查询的结果需要存储在临时表中以提供报告。
I need to create the staging table, but with hundreds of views/tables to dig through to find the data types that are being represented here, I have to wonder if there's a better way to construct this table.
我需要创建临时表，但是有数百个视图/表需要挖掘以找到此处表示的数据类型，我想知道是否有更好的方法来构造此表。
Can anyone advise how I would use any of the SQL Server 2008 tools to divine the source data types in my SQL 2000 database?
谁能建议我如何使用任何 SQL Server 2008 工具来确定 SQL 2000 数据库中的源数据类型？
As a general example, I want to know from a query like:
作为一般示例，我想从以下查询中了解：
SELECT Auth_First_Name, Auth_Last_Name, Auth_Favorite_Number
FROM Authors
Instead of the actual results, I want to know that:
而不是实际结果，我想知道：
Auth_First_Name is char(25)
Auth_Last_Name is char(50)
Auth_Favorite_Number is int
I'm not interested in constraints, I really just want to know the data types.
我对约束不感兴趣，我真的只想知道数据类型。
采纳答案by erikkallen
select * from information_schema.columns
could get you started.
可以让你开始。
回答by redcalx
For SQL Server 2012 and above: If you place the query into a string then you can get the result set data types like so:
对于 SQL Server 2012 及更高版本：如果将查询放入字符串中，则可以获得如下所示的结果集数据类型：
DECLARE @query nvarchar(max) = 'select 12.1 / 10.1 AS [Column1]';
EXEC sp_describe_first_result_set @query, null, 0;
回答by Trisped
You could also insert the results (or top 10 results) into a temp table and get the columns from the temp table (as long as the column names are all different).
您还可以将结果（或前 10 个结果）插入临时表并从临时表中获取列（只要列名都不同）。
SELECT TOP 10 *
INTO #TempTable
FROM <DataSource>
Then use:
然后使用：
EXEC tempdb.dbo.sp_help N'#TempTable';
or
或者
SELECT *
FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID(N'tempdb..#TempTable');
Extrapolated from Aaron's answer here.
回答by Aaron Bertrand
You can also use...
您还可以使用...
SQL_VARIANT_PROPERTY()
...in cases where you don't have direct access to the metadata (e.g. a linked server query perhaps?).
...在您无法直接访问元数据的情况下（例如，可能是链接服务器查询？）。
http://msdn.microsoft.com/en-us/library/ms178550.aspx
http://msdn.microsoft.com/en-us/library/ms178550.aspx
In SQL Server 2005 and beyond you are better off using the catalog views (sys.columns) as opposed to INFORMATION_SCHEMA. Unless portability to other platforms is important. Just keep in mind that the INFORMATION_SCHEMA views won't change and so they will progressively be lacking information on new features etc. in successive versions of SQL Server.
在 SQL Server 2005 及更高版本中，最好使用目录视图 (sys.columns)，而不是 INFORMATION_SCHEMA。除非可移植到其他平台很重要。请记住，INFORMATION_SCHEMA 视图不会更改，因此在 SQL Server 的后续版本中，它们将逐渐缺乏有关新功能等的信息。
回答by Paul M Sorauer
There MUSTbe en easier way to do this... Low and behold, there is...!
有必须是连接更简单的方法来做到这一点...低看哪，有...！
"sp_describe_first_result_set" is your friend!
“ sp_describe_first_result_set”是你的朋友！
Now I do realise the question was asked specifically for SQL Server 2000, but I was looking for a similar solution for later versions and discovered some native support in SQL to achieve this.
现在我确实意识到这个问题是专门针对 SQL Server 2000 提出的，但我一直在为更高版本寻找类似的解决方案，并在 SQL 中发现了一些本机支持来实现这一点。
In SQL Server 2012 onwards cf. "sp_describe_first_result_set" - Link to BOL
在 SQL Server 2012 中，参见。"sp_describe_first_result_set" -链接到 BOL
I had already implemented a solution using a technique similar to @Trisped'sabove and ripped it out to implement the native SQL Server implementation.
我已经使用类似于上面@Trisped 的技术实现了一个解决方案，并将其删除以实现本机 SQL Server 实现。
In case you're not on SQL Server 2012 or Azure SQL Database yet, here's the stored proc I created for pre-2012 era databases:
如果您还没有使用 SQL Server 2012 或 Azure SQL 数据库，这里是我为 2012 年之前的时代数据库创建的存储过程：
CREATE PROCEDURE [fn].[GetQueryResultMetadata]
@queryText VARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
--SET NOCOUNT ON;
PRINT @queryText;
DECLARE
@sqlToExec NVARCHAR(MAX) =
'SELECT TOP 1 * INTO #QueryMetadata FROM ('
+
@queryText
+
') T;'
+ '
SELECT
C.Name [ColumnName],
TP.Name [ColumnType],
C.max_length [MaxLength],
C.[precision] [Precision],
C.[scale] [Scale],
C.[is_nullable] IsNullable
FROM
tempdb.sys.columns C
INNER JOIN
tempdb.sys.types TP
ON
TP.system_type_id = C.system_type_id
AND
-- exclude custom types
TP.system_type_id = TP.user_type_id
WHERE
[object_id] = OBJECT_ID(N''tempdb..#QueryMetadata'');
'
EXEC sp_executesql @sqlToExec
END
回答by Pawel Czapski
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
You can use columns aliases for better looking output.
您可以使用列别名以获得更好看的输出。
回答by LukeH
Can you get away with recreating the staging table from scratch every time the query is executed? If so you could use SELECT ... INTOsyntax and let SQL Server worry about creating the table using the correct column types etc.
每次执行查询时，您都可以从头开始重新创建临时表吗？如果是这样，您可以使用SELECT ... INTO语法并让 SQL Server 担心使用正确的列类型等创建表。
SELECT *
INTO your_staging_table
FROM enormous_collection_of_views_tables_etc
回答by LukeH
This will give you everything column property related.
这将为您提供与列属性相关的所有内容。
SELECT * INTO TMP1
FROM ( SELECT TOP 1 /* rest of your query expression here */ );
SELECT o.name AS obj_name, TYPE_NAME(c.user_type_id) AS type_name, c.*
FROM sys.objects AS o
JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE o.name = 'TMP1';
DROP TABLE TMP1;
回答by Alias Varghese
sp_describe_first_result_set
sp_describe_first_result_set
will help to identify the datatypes of query by analyzing datatypes of first resultset of query
将通过分析查询的第一个结果集的数据类型来帮助识别查询的数据类型
回答by rsidebot
I use a simple case statement to render results I can use in technical specification documents. This example does not contain every condition you will run into with a database, but it gives you a good template to work with.
我使用一个简单的 case 语句来呈现我可以在技术规范文档中使用的结果。此示例并未包含您在使用数据库时会遇到的所有条件，但它为您提供了一个很好的模板。
SELECT
TABLE_NAME AS 'Table Name',
COLUMN_NAME AS 'Column Name',
CASE WHEN DATA_TYPE LIKE '%char'
THEN DATA_TYPE + '(' + CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE IN ('bit', 'int', 'smallint', 'date')
THEN DATA_TYPE
WHEN DATA_TYPE = 'datetime'
THEN DATA_TYPE + '(' + CONVERT(VARCHAR, DATETIME_PRECISION) + ')'
WHEN DATA_TYPE = 'float'
THEN DATA_TYPE
WHEN DATA_TYPE IN ('numeric', 'money')
THEN DATA_TYPE + '(' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR, NUMERIC_PRECISION_RADIX) + ')'
END AS 'Data Type',
CASE WHEN IS_NULLABLE = 'NO'
THEN 'NOT NULL'
ELSE 'NULL'
END AS 'PK/LK/NOT NULL'
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY
TABLE_NAME, ORDINAL_POSITION
