在SQL Server 2008中从任意SQL语句获取数据类型

Question

针对一些任意的SQL语句，我想获取返回列的数据类型。该语句可能会联接多个表、视图、TVFs等等。我知道可以基于查询创建视图并从中获取数据类型，但希望有更快捷的方法。目前唯一能想到的是编写一个.NET工具来运行SQL并检查结果，不确定是否有TSQL解决方案。

---

例如：

给定以下SQL（仅为示例，非真实表）

SELECT p.Name AS PersonName, p.Age, a.Account as AccountName
FROM Person as p
LEFT JOIN Account as a
    ON p.Id = a.OwnerId

我希望有以下这样的内容：

人名：（nvarchar（255），非空）

年龄：（smallInt，非空）

等等...

- Christoph

---

@HLGEM 我需要在数据集市中创建一些表，使其与源系统中的某些查询模式匹配。 - Christoph

Answer 1

    /*you may have to alias some columns if they are not unique*/
    /*EDIT: added fix for 2byte nchar/nvarchar */
    SELECT top (1)  /*<your query columns here>*/ 
    INTO #tmp99
    /*<rest of your query here>*/


    SELECT 'CREATE TABLE [tablename]('  UNION ALL 
    SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN '' ELSE ',' END 
    + CHAR(13) + '[' + c.name + '] [' + t.name + ']' 
    + CASE WHEN c.system_type_id IN (165,167,173,175) 
           THEN CASE WHEN c.max_length <> -1
                     THEN '(' + CAST(c.max_length AS varchar(7)) + ')' 
                     ELSE '(max)' 
                     END 
           WHEN c.system_type_id IN (231,239) 
           THEN CASE WHEN c.max_length <> -1 
                     THEN '(' + CAST(c.max_length/2 AS varchar(7)) + ')' 
                     ELSE '(max)' END
           ELSE 
               CASE WHEN c.system_type_id IN (41,42,43) 
                    THEN '(' + CAST(c.scale AS varchar(7)) + ')'
                    ELSE
                        CASE WHEN c.system_type_id IN (106,108) 
                             THEN '(' + CAST(c.precisiON AS varchar(7)) + ',' + CAST(c.scale AS varchar(7)) + ')'
                             ELSE ''
                             END 
                    END
           END

    + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END 

    FROM tempdb.sys.columns c
    JOIN tempdb..sysobjects o ON (c.object_id = o.id)
    JOIN tempdb.sys.types t ON (t.user_type_id = c.user_type_id)
    WHERE o.name LIKE '#tmp99%' 
    UNION ALL SELECT ')' 
    FOR XML PATH('')

    DROP TABLE #tmp99

- Scot Hauder

---

这很不错，似乎工作得很好。我注意到的唯一问题是双字节字段的长度不正确（即nvarchar(255)变成nvarchar(510)），所以我只是添加了另一个情况，当system_type_id IN (231,239)时，我将长度除以二。 - Christoph

我会在这个周末修复它。我相当快地把它拼凑起来，所以希望它至少能为你提供一个良好的开始，满足你的需求。 - Scot Hauder

谢谢Scot，我刚刚添加了以下内容并从第一个案例中删除了231和239，所以现在它对我很有效。当c.system_type_id IN (231,239)时 然后，如果c.max_length <> -1， 则为'(' + CAST(c.max_length/2 AS varchar(7)) + ')'， 否则为'(max)'。 - Christoph

Answer 2

我试图实现这个目标，但它变得太难了。让我告诉你我是如何尝试的。我使用了 SQL 解析器：http://www.sqlparser.com/download.php 和 C#。然后尝试分析文本。对于简单的查询来说还好，但很快就变得太复杂了。关于视图的想法看起来简单得多。

- Andrey