我在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)
这将让我把刚才复制并粘贴到我的CREATE TABLE语句。
这可能会给你一个开始：
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
编辑：TEMP表：
临时表略有不同，比如这部作品在2008年的SQL的临时表名为#tv_source
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的一部分，我并没有试图返工，对于这个问题。
SELECT
', ['+ac.name+'] '+Type_Name(User_type_id)+
CASE WHEN Type_Name(User_type_id) = 'Decimal'
THEN +'('+CONVERT(Varchar(4),ac.Precision)+','+CONVERT(Varchar(4),ac.Scale)+')'
WHEN Type_Name(User_type_id) IN
('tinyint','smallint','int','real','money','float','numeric','smallmoney','DateTime')
THEN ''
ELSE +'('+CONVERT(Varchar(4),ac.Max_Length)+')'
END AS TableColumn
FROM Tempdb.sys.all_columns AS ac
INNER JOIN Tempdb.Sys.SysObjects AS so
ON so.ID = ac.Object_ID
WHERE 1 = 1
AND so.Name = '##YourTempTableGoesHere'
......我创建了一个功能，可以输出列和数据类型的列表，如果对象是一个表，如果是这样的东西，就对您有用吗？
CREATE FUNCTION [dbo].[fnDiscoverColumns]
( @PObjectName NVARCHAR(300) )
RETURNS @Data TABLE ( ColumnList NVARCHAR(350) )
AS
BEGIN
DECLARE @PObjectID TABLE ( [object_id] INT )
INSERT @PObjectID ( [object_id] )
SELECT [object_id] FROM sys.objects AS O WHERE O.name = @PObjectName AND O.type = 'U'
DECLARE @PObjectDetails TABLE ( [RowNo] INT,[ColumnName] NVARCHAR(300),[XType] INT,[DataType] NVARCHAR(100),[system_type_id] INT,[user_type_id] INT,[MaxLength] NVARCHAR(5),[Precision] INT,[Scale] INT,[ColumnList] NVARCHAR(300) )
INSERT @PObjectDetails ( [RowNo],[ColumnName],[XType],[DataType],[system_type_id],[user_type_id],[MaxLength],[Precision],[Scale],[ColumnList] )
SELECT DISTINCT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'RowNo',
C.name AS 'ColumnName',
T.xtype AS 'XType',
UPPER(T.name) AS 'DataType',
C.system_type_id,
C.user_type_id,
CASE WHEN C.max_length < 0 THEN 'MAX' ELSE CAST(C.max_length AS VARCHAR) END AS 'MaxLength',
C.precision AS 'Precision',
C.scale AS 'Scale',
CASE
WHEN [XType] IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,189,240,241) THEN QUOTENAME(C.name) +' '+ UPPER(T.name) +','
WHEN [XType] IN (106,108) THEN QUOTENAME(C.name) +' '+ UPPER(T.name) +'('+ CAST([Precision] AS VARCHAR) +','+ CAST(C.scale AS VARCHAR) +'),'
WHEN [XType] IN (41,42,43,165,167,173,175,231,239) THEN QUOTENAME(C.name) +' '+ UPPER(T.name) +'('+ CASE WHEN C.max_length < 0 THEN 'MAX' WHEN C.max_length > 1 THEN CAST(C.max_length / 2 AS VARCHAR) ELSE CAST(C.max_length AS VARCHAR) END +'),' ELSE NULL END AS 'ColumnList'
FROM sys.all_columns AS C
JOIN systypes AS T ON C.system_type_id = T.xusertype
WHERE C.object_id = (SELECT * FROM @PObjectID) --373576369
--Return column names and data types
INSERT @Data
SELECT 'CREATE TABLE #ColumnsList ('
INSERT @Data
SELECT
CASE WHEN C.RowNo = (SELECT MAX(RowNo) FROM @PObjectDetails) THEN LEFT(C.ColumnList, ABS(LEN(C.ColumnList + ',') - 2)) ELSE C.ColumnList END AS 'GeneratedColumns'
FROM @PObjectDetails AS C
INSERT @Data
SELECT ')'
RETURN
END
GO
一旦提交到数据库，这样运行：
SELECT * FROM [dbo].[fnDiscoverColumns] ('ExecutionLogStorage') --name of table
这应该给你一个像这样的输出：
CREATE TABLE #ColumnsList (
[LogEntryId] BIGINT,
[InstanceName] NVARCHAR(38),
[ReportID] UNIQUEIDENTIFIER,
[UserName] NVARCHAR(260),
[ExecutionId] NVARCHAR(64),
[RequestType] TINYINT,
[Format] NVARCHAR(26),
[Parameters] NTEXT,
[ReportAction] TINYINT,
[TimeStart] DATETIME,
[TimeEnd] DATETIME,
[TimeDataRetrieval] INT,
[TimeProcessing] INT,
[TimeRendering] INT,
[Source] TINYINT,
[Status] NVARCHAR(40),
[ByteCount] BIGINT,
[RowCount] BIGINT,
[AdditionalInfo] XML
)
