有沒有辦法在 TSQL 中生成表創建腳本？

有沒有辦法純粹在 T-SQL 中從現有表生成創建腳本（即不使用 SMO，因為 T-SQL 無權訪問 SMO）。假設一個儲存過程接收一個表名並返回一個包含給定表的創建腳本的字元串？

現在讓我描述一下我面臨的情況，因為可能有不同的方法來解決這個問題。我有一個包含幾十個數據庫的實例。這些數據庫都具有相同的架構、所有相同的表、索引等。它們是作為第三方軟體安裝的一部分創建的。我需要有一種方法與他們合作，以便我可以以特別的方式從他們那裡聚合數據。dba.se 的好人已經在這裡幫助了我如何在不同的數據庫中創建觸發器？

目前我需要找到一種方法來從所有數據庫的表中進行選擇。我已將所有數據庫名稱記錄到一個名為的表中Databasees，並編寫了以下腳本來對所有數據庫執行 select 語句：

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) = 
 N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

 DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
 EXEC @sql @statement

 SET @LastDatabaseID = @DatabaseIDToHandle
 SET @DatabaseIDToHandle = NULL

 SELECT TOP 1 @DatabaseNameToHandle = Name,
 @DatabaseIDToHandle = Database_Ref_No
 FROM Databasees
 WHERE Database_Ref_No > @LastDatabaseID
 ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

但是，上面的腳本失敗並顯示以下消息：

只有在使用列列表並且 IDENTITY_INSERT 為 ON 時，才能為表“#tmp”中的標識列指定顯式值。

添加這個：

SET IDENTITY_INSERT #tmp ON

沒有幫助，因為我無法指定列列表並使其保持通用。

在 SQL 中，無法關閉給定表上的標識。您只能刪除一列並添加一列，這顯然會更改列順序。如果列順序發生變化，您需要再次指定列列表，這將根據您查詢的表而有所不同。

所以我在想，如果我可以在我的 T-SQL 程式碼中獲得創建表腳本，我可以使用字元串操作表達式來操作它以刪除標識列，並將數據庫名稱的列添加到結果集中。

誰能想到一個相對簡單的方法來實現我想要的？

CREATE TABLE早在 2007 年，我就要求一種通過 T-SQL 而不是使用 UI 或 SMO生成腳本的簡單方法。我被當場拒絕了。

但是，SQL Server 2012 使這變得非常容易。假設我們有一個跨多個數據庫具有相同架構的表，例如dbo.whatcha：

CREATE TABLE dbo.whatcha
(
 id INT IDENTITY(1,1), 
 x VARCHAR(MAX), 
 b DECIMAL(10,2), 
 y SYSNAME
);

以下腳本使用新的sys.dm_exec_describe_first_results_set動態管理功能來檢索每個列的正確數據類型（並忽略IDENTITY屬性）。它建構您需要的#tmp 表，從列表中的每個數據庫中插入，然後從#tmp 中進行選擇，所有這些都在單個動態 SQL 批處理中並且不使用WHILE循環（這並沒有使它變得更好，只是更簡單查看並允許您Database_Ref_No完全忽略:-))。

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
 FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
 -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
 INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
 FROM @dbs;

SET @sql += N'
 SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

結果PRINT輸出：

CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
 INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
 INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
 SELECT id,x,b,y FROM #tmp;

當您確信它正在執行您期望的操作時，只需取消註釋EXEC.

（這讓您相信架構是相同的；它不會驗證一個或多個表已被更改，因此可能會失敗。）

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