如何从查询返回SQL数据类型?

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 - cppap - fap - add)，我不需要也不想理解。此查询的结果需要存储在staging表中，以提供报告。

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.

我需要创建staging表，但是需要挖掘数百个视图/表来查找这里表示的数据类型，我必须考虑是否有更好的方法来构建这个表。

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.

我对约束不感兴趣，我只想知道数据类型。

9 个解决方案

#1

select * from information_schema.columns

could get you started.

可以让你开始。

#2

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

或

SELECT * 
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..#TempTable');

Extrapolated from Aaron's answer here.

根据Aaron的回答。

#3

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

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和beyond中，最好使用catalog视图(sys.column)，而不是INFORMATION_SCHEMA。除非移植到其他平台很重要。请记住，INFORMATION_SCHEMA视图不会改变，因此在SQL Server的后续版本中，它们将逐渐缺乏关于新特性等的信息。

#4

There MUST be 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开始的cf。“sp_descripbe_first_result_set”-到BOL的链接

I had already implemented a solution using a technique similar to @Trisped's above and ripped it out to implement the native SQL Server implementation.

我已经使用类似于@ trispeed的方法实现了一个解决方案，并将其删除，以实现本机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年以前的数据库创建的存储proc:

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

#5

Can you get away with recreating the staging table from scratch every time the query is executed? If so you could use SELECT ... INTO syntax and let SQL Server worry about creating the table using the correct column types etc.

您能在每次执行查询时从头重新创建staging表吗?如果是这样，您可以使用SELECT…加入语法，让SQL Server担心使用正确的列类型创建表等。

SELECT *
INTO your_staging_table
FROM enormous_collection_of_views_tables_etc

#6

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.

可以使用列别名来更好地显示输出。

#7

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;

#8

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;

#9

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='yourTable';

#1

select * from information_schema.columns