SQL Server의 기존 테이블에 대한 SQL CREATE 스크립트를 생성하는 방법은 무엇입니까?

기존 테이블에 대한 SQL 생성 스크립트 생성

SQL Server에서는 sys.tables를 직접 사용하는 대신 다음 쿼리를 활용하여 기존 테이블에 대한 CREATE 스크립트 생성:

DECLARE @table_name SYSNAME;
SELECT @table_name = 'dbo.WorkOut';

DECLARE 
  @object_name SYSNAME,
  @object_id INT;

SELECT 
  @object_name = '[' + s.name + '].[' + o.name + ']',
  @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
  AND o.[type] = 'U'
  AND o.is_ms_shipped = 0;

DECLARE @SQL NVARCHAR(MAX) = '';

WITH index_column AS
(
  SELECT 
    ic.[object_id],
    ic.index_id,
    ic.is_descending_key,
    ic.is_included_column,
    c.name
  FROM sys.index_columns ic WITH (NOWAIT)
  JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
  WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
  SELECT 
    k.constraint_object_id,
    cname = c.name,
    rcname = rc.name
  FROM sys.foreign_key_columns k WITH (NOWAIT)
  JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
  JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
  WHERE k.parent_object_id = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
  SELECT CHAR(9) + ', [' + c.name + '] ' + 
      CASE WHEN c.is_computed = 1
          THEN 'AS ' + cc.[definition] 
          ELSE UPPER(tp.name) + 
              CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                     THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                   WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                     THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                   WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                     THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name IN ('decimal', 'numeric')
                     THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END 
      END + CHAR(13)
  FROM sys.columns c WITH (NOWAIT)
  JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
  LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
  LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
  LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
  WHERE c.[object_id] = @object_id
  ORDER BY c.column_id
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
  + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' + 
                  (SELECT STUFF((
                       SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                       FROM sys.index_columns ic WITH (NOWAIT)
                       JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                       WHERE ic.is_included_column = 0
                           AND ic.[object_id] = k.parent_object_id 
                           AND ic.index_id = k.unique_index_id     
                       FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
              + ')' + CHAR(13)
              FROM sys.key_constraints k WITH (NOWAIT)
              WHERE k.parent_object_id = @object_id 
                  AND k.[type] = 'PK'), '') + ')'  + CHAR(13)
  + ISNULL((SELECT (
      SELECT CHAR(13) +
           'ALTER TABLE ' + @object_name + ' WITH' 
          + CASE WHEN fk.is_not_trusted = 1 
              THEN ' NOCHECK' 
              ELSE ' CHECK' 
            END + 
            ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
            + STUFF((
              SELECT ', [' + k.cname + ']'
              FROM fk_columns k
              WHERE k.constraint_object_id = fk.[object_id]
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
             + ')' +
            ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
            + STUFF((
              SELECT ', [' + k.rcname + ']'
              FROM fk_columns k
              WHERE k.constraint_object_id = fk.[object_id]
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
             + ')'
          + CASE 
              WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
              WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
              WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
              ELSE '' 
            END
          + CASE 
              WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
              WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
              WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
              ELSE '' 
            END 
          + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
      FROM sys.foreign_keys fk WITH (NOWAIT)
      JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
      WHERE fk.parent_object_id = @object_id
      FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
  + ISNULL(((SELECT
       CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END 
              + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
              STUFF((
              SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END

위 내용은 SQL Server의 기존 테이블에 대한 SQL CREATE 스크립트를 생성하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!