將不包括可空性的表結構複製到本地臨時表中

如何將表的結構複製到臨時表（或表變數）中，不包括每列**的可空性？**主要是我需要複製列名和數據類型，而不是可空性、鍵、索引等。我還要注意，這必須在過程中以程式方式完成，所以我不能只生成create table腳本並修改它。我想出的最好的是：

select top 0 *
into #MyTempTable
from dbo.MyTable

它複製了每個欄位的可空性，從而違背了我的目的。

我也玩過動態 SQL 並從表中提取列數據INFORMATION_SCHEMA.COLUMNS來建構create table語句，但問題是在動態 SQL 語句執行後臨時表超出範圍，控制權返回到“主”過程. （而且我寧願不要將過程的其餘部分塞進動態 SQL 語句中。）如果有辦法從exec (@Sql)語句中返回臨時表或以某種方式將其保持在範圍內，它可能會起作用，但我不知道有辦法做到這一點。

相關問題：

• 複製表的完整結構
• 從現有表創建表結構

對於表變數，您無能為力。但是，對於臨時表（甚至是本地表——即名稱以單個 開頭的表#），可以做的事情還很多。

關於本地臨時表在創建它們的過程結束後無法倖存的行為，人們似乎陷入困境的地方是假設它們只能被創建。這對於表變數可能是正確的（這就是為什麼它們在這裡沒有幫助），但對於臨時表（本地或全域）來說並非如此：它們也可以被改變！並且，因為本地臨時表對子程序可用，並且因為在子程序中對它們所做的任何更改在該子程序結束後仍然存在，所以它們可以在“目前”範圍內創建，然後在子程序中進行更改-過程。最終結果將是目前範圍內所需的結構:-)！

因此，您不需要根據需要創建具有完美結構的本地臨時表來解決這個問題，只要有一種程式方式來確定應該進行哪些更改即可。對於這個特殊的請求（謝天謝地）並不是非常困難。ALTER TABLE創建臨時表後，我們只需查看源表的列以獲取它們的名稱、數據類型和可選的排序規則（語句所需的三部分）。ALTER TABLE然後，我們只需使用根據需要調整可空性的語句構造一個動態 SQL 字元串。執行此操作，世界一切都會好起來的。

設置測試：

對於這個測試，我們將使用本地臨時表作為要複製的表，但這只是為了使測試更容易，永久表的行為方式相同。

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#SourceTable') IS NOT NULL)
BEGIN
 PRINT 'Dropping #SourceTable...';
 DROP TABLE #SourceTable;
END;

CREATE TABLE #SourceTable
(
 Col1 INT,
 Col2 NVARCHAR(37) COLLATE Hungarian_100_CI_AI_SC NOT NULL,
 Col3 DATE,
 Col4 TIME(3) NOT NULL,
 Col5 FLOAT(20) NOT NULL,
 Col6 DECIMAL(10,3) NOT NULL,
 Col7 NUMERIC(14)
);

IF (OBJECT_ID(N'tempdb..#TargetTable') IS NOT NULL)
BEGIN
 PRINT 'Dropping #TargetTable...';
 DROP TABLE #TargetTable;
END;

執行測試：

找到可空性相當容易。但是您不能在不指定數據類型的情況下發出ALTER COLUMN語句。該數據類型由sys.dm_exec_describe_first_result_set()DMF 以正確的格式提供，但這是在 SQL Server 2012 中引入的，因此它對 SQL Server 2008 R2 沒有幫助。因此，我們必須重建實際的數據類型，這意味著您需要： 除以max_length2NCHAR和NVARCHAR; scale單獨用於time,datetime2和datetimeoffset; 並同時使用scaleand precisionfor decimaland numeric( <sarcasm> so much fun </sarcasm> )。

注意：在測試場景之外使用時，請記住刪除下面程式碼中的兩個引用tempdb.。這些引用——在程式碼的內聯註釋中註明——只是因為源表（要複製的表）是一個臨時表並且存在於tempdb.

SELECT *
INTO   #TargetTable
FROM   #SourceTable
WHERE  1 = 0;

DECLARE @AlterQuery NVARCHAR(MAX);
SET @AlterQuery = N'';

SELECT  @AlterQuery = @AlterQuery
       + N'ALTER TABLE #TargetTable ALTER COLUMN [' + col.[name] + N'] '
       + CASE
           WHEN col.user_type_id IN (34, 35, 36, 40, 48, 52, 56, 58, 59, 60, 61, 62, 98,
                           99, 104, 122, 127, 128, 129, 130, 189, 241, 256) THEN tp.name
           WHEN col.system_type_id = 240 THEN tp.name -- User-Defined Types (UDTs)
           WHEN col.user_type_id IN (231, 239) THEN tp.name + N'(' +
                      CONVERT(NVARCHAR(5), col.max_length / 2) + N')' -- nvarchar, nchar
           WHEN col.user_type_id IN (165, 167, 173, 175) THEN tp.name + N'(' +
                   CONVERT(NVARCHAR(5), col.max_length) + N')' -- (var)binary, (var)char
           WHEN col.user_type_id IN (41, 42, 43) THEN tp.name + N'(' +
               CONVERT(NVARCHAR(5), col.scale) + N')' -- time, datetime2, datetimeoffset
           WHEN col.user_type_id IN (106, 108) THEN tp.name + N'(' + 
                           CONVERT(NVARCHAR(5), col.[precision]) + N', ' +
                           CONVERT(NVARCHAR(5), col.scale) + N')' -- decimal, numeric
         END
       + ISNULL(N' COLLATE ' + col.collation_name, N'')
       + N' NULL;' + NCHAR(0x0D) + NCHAR(0x0A) -- CR + LF
FROM     tempdb.sys.columns col -- remove "tempdb."
INNER JOIN sys.types tp
       ON tp.user_type_id = col.user_type_id
WHERE    col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable') -- remove "tempdb..#"
AND      col.is_identity <> 1 -- exclude the IDENTITY field
AND      col.is_nullable = 0 -- only ALTER the columns that actually need it
ORDER BY col.column_id ASC;

PRINT @AlterQuery; -- debug

EXEC (@AlterQuery);

檢查結果：

以下查詢將顯示列及其數據類型和可空性。您將看到所有欄位現在都is_nullable顯示為1:-)。

SELECT col.column_id,
      col.[name],
      tp.[name],
      col.max_length,
      col.[precision],
      col.scale,
      col.is_nullable
FROM   tempdb.sys.columns col
INNER JOIN sys.types tp
       ON tp.user_type_id = col.user_type_id
WHERE  col.[object_id] = OBJECT_ID(N'tempdb..#TargetTable')
ORDER BY col.column_id ASC;

引用自：https://dba.stackexchange.com/questions/120859