Rumah > pangkalan data > tutorial mysql > Bagaimana untuk Menghasilkan Skrip SQL CREATE untuk Jadual Sedia Ada dalam SQL Server?

Bagaimana untuk Menghasilkan Skrip SQL CREATE untuk Jadual Sedia Ada dalam SQL Server?

DDD
Lepaskan: 2025-01-14 08:37:45
asal
305 orang telah melayarinya

How to Generate SQL CREATE Scripts for Existing Tables in SQL Server?

Jana Skrip Buat SQL untuk Jadual Sedia Ada

Dalam Pelayan SQL, bukannya menggunakan sys.tables secara langsung, anda boleh menggunakan pertanyaan berikut untuk jana skrip CREATE untuk jadual sedia ada:

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
Salin selepas log masuk

Atas ialah kandungan terperinci Bagaimana untuk Menghasilkan Skrip SQL CREATE untuk Jadual Sedia Ada dalam SQL Server?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan