使用查詢產生現有資料表的 SQL CREATE 腳本
雖然此查詢本身不會直接產生 CREATE 腳本,但它可以從 SQL Server 儲存的模式資訊派生出來。
方法:
代碼:
<code class="language-sql">DECLARE @table_name SYSNAME = 'dbo.MyTable'; DECLARE @DDL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@table_name) + ' ('; SELECT @DDL += CHAR(13) + ' [' + c.name + '] ' + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' FROM sys.columns c WHERE c.[object_id] = OBJECT_ID(@table_name); SET @DDL = LEFT(@DDL, LEN(@DDL) - 1) + ')'; SELECT @DDL += CHAR(13) + ' CONSTRAINT [PK_' + @table_name + '] PRIMARY KEY (' + STUFF((SELECT ', [' + c.name + ']' FROM sys.index_columns c WHERE c.[object_id] = OBJECT_ID(@table_name) AND c.index_id = 1), 1, 2, '') + ')'; SELECT @DDL += CHAR(13) + ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF);'; PRINT @DDL;</code>
輸出範例:
<code class="language-sql">CREATE TABLE [dbo].[MyTable] ( [ID] INT NOT NULL, [Name] VARCHAR(50) NULL, [Age] INT NULL, CONSTRAINT [PK_MyTable] PRIMARY KEY ([ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF);</code>
以上是如何從現有表產生 SQL CREATE 表腳本?的詳細內容。更多資訊請關注PHP中文網其他相關文章!