sql - 如何通过脚本自动生成临时表列和数据类型

Question

我经常在 SQL 中创建临时表，我正在研究一种为表定义自动生成列名和数据类型的方法，这样我就不必每次都查找它们。

例如我运行:

SELECT CustomerID
  ClientID,
  FirstName
  LastName
INTO #Test
From dbo.Customer

首先使用我需要的适当列和数据设置临时表。完成所有这些后，我将返回并取出 INTO 语句并编写以下内容:

CREATE TABLE #Test
(
...
...
);

我想找到一种方法来从临时表的初始创建中自动生成列名和数据类型。现在，因为我最初是插入到一个自动创建的临时表中，所以我使用这个:

EXEC tempdb..sp_help '#Test';

这为我提供了我需要的一切，而无需查找所有列数据类型，但我想知道是否有一种方法可以从类似这样的东西中自动生成列名。所以自动生成器会生成:

CustomerID int,
ClientID int,
FirstName varchar(50),
LastName varchar(50)

这将允许我将其复制并粘贴到我的创建表语句中。

Answer 1

这可能会给你一个开始:

DECLARE @viewname VARCHAR(50);
SET @viewname ='tableorviewname';
SELECT  c.name + ' '+ t.name + 
case t.name  
WHEN 'varchar' THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'
ELSE ',' 
end
FROM sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id = (SELECT object_id from sys.objects where name = @viewname)
ORDER BY c.column_id

编辑:临时表:

临时表略有不同，例如这适用于名为#tv_source 的临时表的 sql 2008

DECLARE @viewortablename VARCHAR(50);
SET @viewortablename ='tempdb..#tv_source';
SELECT  c.name + ' '+ t.name + 
case t.name  
WHEN 'varchar' THEN '('+CAST(c.max_length AS VARCHAR(3) )+'),'
ELSE ',' 
end
FROM tempdb.sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id =  object_id(@viewortablename)
ORDER BY c.column_id

注意:这给出了一个逗号分隔的列表，但没有尝试删除最后一个逗号，它只给出了一个列表，您可能希望将其放在字符串上并进行操作等，然后用作动态 sql 或东西。不过，它应该能让您开始想要做的事情。

请注意，对于其他人，sql 2000 不会正确显示长度，例如在 varchar(45) 上，它只会列出 varchar 部分，我没有尝试针对这个问题重新处理它。