gpt4 book ai didi

sql - 从SQL Server 2008中的任意sql语句获取数据类型

转载 作者:行者123 更新时间:2023-12-02 04:16:37 26 4
gpt4 key购买 nike

给定一些任意的SQL,我想获取返回列的数据类型。该语句可能会连接许多表, View ,TVF等。我知道我可以根据查询创建一个 View 并从中获取数据类型,希望有一种更快的方法。只想到我已经想到编写一个.net实用程序来运行SQL并检查结果,想知道是否有TSQL答案。



给定(不是真实表,仅是示例)

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

我想要一些类似的东西

PersonName:(nvarchar(255),不为null)

年龄:(smallInt,不为null)

等等...

最佳答案

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

关于sql - 从SQL Server 2008中的任意sql语句获取数据类型,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2562521/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com