当前位置：Gxlcms > 数据库问题 > 批量生成表Create　SQL　示例　Generate SQL Create Scripts for existing tables with Query

批量生成表Create　SQL　示例　Generate SQL Create Scripts for existing tables with Query

时间：2021-07-01 10:21:17 帮助过：20人阅读

TABLE [dbo].[WorkOut]( [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL, [TimeSheetDate] [datetime] NOT NULL, [DateOut] [datetime] NOT NULL, [EmployeeID] [int] NOT NULL, [IsMainWorkPlace] [bit] NOT NULL, [DepartmentUID] [uniqueidentifier] NOT NULL, [WorkPlaceUID] [uniqueidentifier] NULL, [TeamUID] [uniqueidentifier] NULL, [WorkShiftCD] [nvarchar](10) NULL, [WorkHours] [real] NULL, [AbsenceCode] [varchar](25) NULL, [PaymentType] [char](2) NULL, [CategoryID] [int] NULL, [Year] AS (datepart(year,[TimeSheetDate])), CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED ( [WorkOutID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[WorkOut] ADD CONSTRAINT [DF__WorkOut__IsMainW__2C1E8537] DEFAULT ((1)) FOR [IsMainWorkPlace] ALTER TABLE [dbo].[WorkOut] WITH CHECK ADD CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]

生成用的SQL文

  1. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@table_name</span><span style="color: #000000"> SYSNAME
  2. </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@table_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.WorkOut</span><span style="color: #ff0000">‘</span>
  3. <span style="color: #0000ff">DECLARE</span> 
  4.       <span style="color: #008000">@object_name</span><span style="color: #000000"> SYSNAME
  5.     , </span><span style="color: #008000">@object_id</span> <span style="color: #0000ff">INT</span>
  6. <span style="color: #0000ff">SELECT</span> 
  7.       <span style="color: #008000">@object_name</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">[</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> s.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">].[</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> o.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span><span style="color: #000000">
  8.     , </span><span style="color: #008000">@object_id</span> <span style="color: #808080">=</span> o.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span>
  9. <span style="color: #0000ff">FROM</span> sys.objects o <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  10. </span><span style="color: #808080">JOIN</span> sys.schemas s <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> o.<span style="color: #ff0000">[</span><span style="color: #ff0000">schema_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> s.<span style="color: #ff0000">[</span><span style="color: #ff0000">schema_id</span><span style="color: #ff0000">]</span>
  11. <span style="color: #0000ff">WHERE</span> s.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> o.name <span style="color: #808080">=</span> <span style="color: #008000">@table_name</span>
  12.     <span style="color: #808080">AND</span> o.<span style="color: #ff0000">[</span><span style="color: #ff0000">type</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">U</span><span style="color: #ff0000">‘</span>
  13.     <span style="color: #808080">AND</span> o.is_ms_shipped <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span>
  14. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@SQL</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #ff00ff">MAX</span>) <span style="color: #808080">=</span> <span style="color: #ff0000">‘‘</span><span style="color: #000000">
  15. ;</span><span style="color: #0000ff">WITH</span> index_column <span style="color: #0000ff">AS</span><span style="color: #000000"> 
  16. (
  17.     </span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> 
  18.           ic.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span><span style="color: #000000">
  19.         , ic.index_id
  20.         , ic.is_descending_key
  21.         , ic.is_included_column
  22.         , c.name
  23.     </span><span style="color: #0000ff">FROM</span> sys.index_columns ic <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  24.     </span><span style="color: #808080">JOIN</span> sys.columns c <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> ic.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">AND</span> ic.column_id <span style="color: #808080">=</span><span style="color: #000000"> c.column_id
  25.     </span><span style="color: #0000ff">WHERE</span> ic.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span><span style="color: #000000">
  26. ),
  27. fk_columns </span><span style="color: #0000ff">AS</span><span style="color: #000000"> 
  28. (
  29.      </span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> 
  30.           k.constraint_object_id
  31.         , cname </span><span style="color: #808080">=</span><span style="color: #000000"> c.name
  32.         , rcname </span><span style="color: #808080">=</span><span style="color: #000000"> rc.name
  33.     </span><span style="color: #0000ff">FROM</span> sys.foreign_key_columns k <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  34.     </span><span style="color: #808080">JOIN</span> sys.columns rc <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> rc.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> k.referenced_object_id <span style="color: #808080">AND</span> rc.column_id <span style="color: #808080">=</span><span style="color: #000000"> k.referenced_column_id 
  35.     </span><span style="color: #808080">JOIN</span> sys.columns c <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> k.parent_object_id <span style="color: #808080">AND</span> c.column_id <span style="color: #808080">=</span><span style="color: #000000"> k.parent_column_id
  36.     </span><span style="color: #0000ff">WHERE</span> k.parent_object_id <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span><span style="color: #000000">
  37. )
  38. </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@SQL</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">CREATE TABLE </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@object_name</span> <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span> <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  39.     </span><span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">9</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> c.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> 
  40.         <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.is_computed <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>
  41.             <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">AS </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> cc.<span style="color: #ff0000">[</span><span style="color: #ff0000">definition</span><span style="color: #ff0000">]</span> 
  42.             <span style="color: #0000ff">ELSE</span> <span style="color: #ff00ff">UPPER</span>(tp.name) <span style="color: #808080">+</span> 
  43.                 <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> tp.name <span style="color: #808080">IN</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">char</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">varbinary</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">binary</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">text</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
  44.                        </span><span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.max_length <span style="color: #808080">=</span> <span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">MAX</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff00ff">CAST</span>(c.max_length <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">5</span>)) <span style="color: #0000ff">END</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>
  45.                      <span style="color: #0000ff">WHEN</span> tp.name <span style="color: #808080">IN</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">nvarchar</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">nchar</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">ntext</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
  46.                        </span><span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.max_length <span style="color: #808080">=</span> <span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">MAX</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff00ff">CAST</span>(c.max_length <span style="color: #808080">/</span> <span style="color: #800000; font-weight: bold">2</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">5</span>)) <span style="color: #0000ff">END</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>
  47.                      <span style="color: #0000ff">WHEN</span> tp.name <span style="color: #808080">IN</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">datetime2</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">time2</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">datetimeoffset</span><span style="color: #ff0000">‘</span><span style="color: #000000">) 
  48.                        </span><span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(c.scale <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">5</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>
  49.                      <span style="color: #0000ff">WHEN</span> tp.name <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">decimal</span><span style="color: #ff0000">‘</span> 
  50.                        <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(c.<span style="color: #ff0000">[</span><span style="color: #ff0000">precision</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">5</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(c.scale <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">5</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>
  51.                     <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span>
  52.                 <span style="color: #0000ff">END</span> <span style="color: #808080">+</span>
  53.                 <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.collation_name <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> COLLATE </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> c.collation_name <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> <span style="color: #0000ff">END</span> <span style="color: #808080">+</span>
  54.                 <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.is_nullable <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> NULL</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> NOT NULL</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">END</span> <span style="color: #808080">+</span>
  55.                 <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> dc.<span style="color: #ff0000">[</span><span style="color: #ff0000">definition</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> DEFAULT</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> dc.<span style="color: #ff0000">[</span><span style="color: #ff0000">definition</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> <span style="color: #0000ff">END</span> <span style="color: #808080">+</span> 
  56.                 <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> ic.is_identity <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> IDENTITY(</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #ff00ff">ISNULL</span>(ic.seed_value, <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">AS</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">1</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #ff00ff">ISNULL</span>(ic.increment_value, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">AS</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">1</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> <span style="color: #0000ff">END</span> 
  57.         <span style="color: #0000ff">END</span> <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span><span style="color: #000000">)
  58.     </span><span style="color: #0000ff">FROM</span> sys.columns c <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  59.     </span><span style="color: #808080">JOIN</span> sys.types tp <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.user_type_id <span style="color: #808080">=</span><span style="color: #000000"> tp.user_type_id
  60.     </span><span style="color: #808080">LEFT</span> <span style="color: #808080">JOIN</span> sys.computed_columns cc <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> cc.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">AND</span> c.column_id <span style="color: #808080">=</span><span style="color: #000000"> cc.column_id
  61.     </span><span style="color: #808080">LEFT</span> <span style="color: #808080">JOIN</span> sys.default_constraints dc <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.default_object_id <span style="color: #808080">!=</span> <span style="color: #800000; font-weight: bold">0</span> <span style="color: #808080">AND</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> dc.parent_object_id <span style="color: #808080">AND</span> c.column_id <span style="color: #808080">=</span><span style="color: #000000"> dc.parent_column_id
  62.     </span><span style="color: #808080">LEFT</span> <span style="color: #808080">JOIN</span> sys.identity_columns ic <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.is_identity <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">AND</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> ic.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">AND</span> c.column_id <span style="color: #808080">=</span><span style="color: #000000"> ic.column_id
  63.     </span><span style="color: #0000ff">WHERE</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span>
  64.     <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> c.column_id
  65.     </span><span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">9</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span> <span style="color: #ff0000">‘</span><span style="color: #000000">)
  66.     </span><span style="color: #808080">+</span> <span style="color: #ff00ff">ISNULL</span>((<span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">9</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, CONSTRAINT [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> k.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] PRIMARY KEY (</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span><span style="color: #000000"> 
  67.                     (</span><span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  68.                          </span><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> c.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> ic.is_descending_key <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">DESC</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">ASC</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">END</span>
  69.                          <span style="color: #0000ff">FROM</span> sys.index_columns ic <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  70.                          </span><span style="color: #808080">JOIN</span> sys.columns c <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> ic.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">AND</span> c.column_id <span style="color: #808080">=</span><span style="color: #000000"> ic.column_id
  71.                          </span><span style="color: #0000ff">WHERE</span> ic.is_included_column <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span>
  72.                              <span style="color: #808080">AND</span> ic.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span><span style="color: #000000"> k.parent_object_id 
  73.                              </span><span style="color: #808080">AND</span> ic.index_id <span style="color: #808080">=</span><span style="color: #000000"> k.unique_index_id     
  74.                          </span><span style="color: #0000ff">FOR</span> XML PATH(N<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘‘</span><span style="color: #000000">))
  75.             </span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span><span style="color: #000000">)
  76.             </span><span style="color: #0000ff">FROM</span> sys.key_constraints k <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  77.             </span><span style="color: #0000ff">WHERE</span> k.parent_object_id <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span> 
  78.                 <span style="color: #808080">AND</span> k.<span style="color: #ff0000">[</span><span style="color: #ff0000">type</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">PK</span><span style="color: #ff0000">‘</span>), <span style="color: #ff0000">‘‘</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>  <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span><span style="color: #000000">)
  79.     </span><span style="color: #808080">+</span> <span style="color: #ff00ff">ISNULL</span>((<span style="color: #0000ff">SELECT</span><span style="color: #000000"> (
  80.         </span><span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span>
  81.              <span style="color: #ff0000">‘</span><span style="color: #ff0000">ALTER TABLE </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@object_name</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> WITH</span><span style="color: #ff0000">‘</span> 
  82.             <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> fk.is_not_trusted <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> 
  83.                 <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> NOCHECK</span><span style="color: #ff0000">‘</span> 
  84.                 <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> CHECK</span><span style="color: #ff0000">‘</span> 
  85.               <span style="color: #0000ff">END</span> <span style="color: #808080">+</span> 
  86.               <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ADD CONSTRAINT [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> fk.name  <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] FOREIGN KEY(</span><span style="color: #ff0000">‘</span> 
  87.               <span style="color: #808080">+</span> <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  88.                 </span><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> k.cname <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span>
  89.                 <span style="color: #0000ff">FROM</span><span style="color: #000000"> fk_columns k
  90.                 </span><span style="color: #0000ff">WHERE</span> k.constraint_object_id <span style="color: #808080">=</span> fk.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span>
  91.                 <span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘‘</span><span style="color: #000000">)
  92.                </span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span>
  93.               <span style="color: #ff0000">‘</span><span style="color: #ff0000"> REFERENCES [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> SCHEMA_NAME(ro.<span style="color: #ff0000">[</span><span style="color: #ff0000">schema_id</span><span style="color: #ff0000">]</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">].[</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> ro.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] (</span><span style="color: #ff0000">‘</span>
  94.               <span style="color: #808080">+</span> <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  95.                 </span><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> k.rcname <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span>
  96.                 <span style="color: #0000ff">FROM</span><span style="color: #000000"> fk_columns k
  97.                 </span><span style="color: #0000ff">WHERE</span> k.constraint_object_id <span style="color: #808080">=</span> fk.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span>
  98.                 <span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘‘</span><span style="color: #000000">)
  99.                </span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>
  100.             <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> 
  101.                 <span style="color: #0000ff">WHEN</span> fk.delete_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON DELETE CASCADE</span><span style="color: #ff0000">‘</span> 
  102.                 <span style="color: #0000ff">WHEN</span> fk.delete_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">2</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON DELETE SET NULL</span><span style="color: #ff0000">‘</span>
  103.                 <span style="color: #0000ff">WHEN</span> fk.delete_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">3</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON DELETE SET DEFAULT</span><span style="color: #ff0000">‘</span> 
  104.                 <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> 
  105.               <span style="color: #0000ff">END</span>
  106.             <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> 
  107.                 <span style="color: #0000ff">WHEN</span> fk.update_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON UPDATE CASCADE</span><span style="color: #ff0000">‘</span>
  108.                 <span style="color: #0000ff">WHEN</span> fk.update_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">2</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON UPDATE SET NULL</span><span style="color: #ff0000">‘</span>
  109.                 <span style="color: #0000ff">WHEN</span> fk.update_referential_action <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">3</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ON UPDATE SET DEFAULT</span><span style="color: #ff0000">‘</span>  
  110.                 <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> 
  111.               <span style="color: #0000ff">END</span> 
  112.             <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">ALTER TABLE </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@object_name</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> CHECK CONSTRAINT [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> fk.name  <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span><span style="color: #000000">)
  113.         </span><span style="color: #0000ff">FROM</span> sys.foreign_keys fk <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  114.         </span><span style="color: #808080">JOIN</span> sys.objects ro <span style="color: #0000ff">WITH</span> (NOWAIT) <span style="color: #0000ff">ON</span> ro.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span><span style="color: #000000"> fk.referenced_object_id
  115.         </span><span style="color: #0000ff">WHERE</span> fk.parent_object_id <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span>
  116.         <span style="color: #0000ff">FOR</span> XML PATH(N<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>)), <span style="color: #ff0000">‘‘</span><span style="color: #000000">)
  117.     </span><span style="color: #808080">+</span> <span style="color: #ff00ff">ISNULL</span>(((<span style="color: #0000ff">SELECT</span>
  118.          <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">CREATE</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> i.is_unique <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> UNIQUE</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘‘</span> <span style="color: #0000ff">END</span> 
  119.                 <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> NONCLUSTERED INDEX [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> i.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">] ON </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@object_name</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> (</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span>
  120.                 <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  121.                 </span><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> c.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CASE</span> <span style="color: #0000ff">WHEN</span> c.is_descending_key <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> DESC</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">ELSE</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ASC</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">END</span>
  122.                 <span style="color: #0000ff">FROM</span><span style="color: #000000"> index_column c
  123.                 </span><span style="color: #0000ff">WHERE</span> c.is_included_column <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span>
  124.                     <span style="color: #808080">AND</span> c.index_id <span style="color: #808080">=</span><span style="color: #000000"> i.index_id
  125.                 </span><span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘‘</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>  
  126.                 <span style="color: #808080">+</span> <span style="color: #ff00ff">ISNULL</span>(<span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">INCLUDE (</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> 
  127.                     <span style="color: #ff00ff">STUFF</span><span style="color: #000000">((
  128.                     </span><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">, [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> c.name <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span>
  129.                     <span style="color: #0000ff">FROM</span><span style="color: #000000"> index_column c
  130.                     </span><span style="color: #0000ff">WHERE</span> c.is_included_column <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span>
  131.                         <span style="color: #808080">AND</span> c.index_id <span style="color: #808080">=</span><span style="color: #000000"> i.index_id
  132.                     </span><span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span>), <span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #ff0000">‘‘</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘‘</span>)  <span style="color: #808080">+</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">13</span><span style="color: #000000">)
  133.         </span><span style="color: #0000ff">FROM</span> sys.indexes i <span style="color: #0000ff">WITH</span><span style="color: #000000"> (NOWAIT)
  134.         </span><span style="color: #0000ff">WHERE</span> i.<span style="color: #ff0000">[</span><span style="color: #ff0000">object_id</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #008000">@object_id</span>
  135.             <span style="color: #808080">AND</span> i.is_primary_key <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span>
  136.             <span style="color: #808080">AND</span> i.<span style="color: #ff0000">[</span><span style="color: #ff0000">type</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">2</span>
  137.         <span style="color: #0000ff">FOR</span> XML PATH(<span style="color: #ff0000">‘‘</span>), TYPE).value(<span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">NVARCHAR(MAX)</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
  138.     ), </span><span style="color: #ff0000">‘‘</span><span style="color: #000000">)
  139. </span><span style="color: #0000ff">PRINT</span> <span style="color: #008000">@SQL</span>
  140. <span style="color: #008080">--</span><span style="color: #008080">EXEC sys.sp_executesql @SQL</span>

执行结果：

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOut</span><span style="color: #ff0000">]</span><span style="color: #000000">
  2. (
  3.       </span><span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOutID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">BIGINT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #ff00ff">IDENTITY</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  4.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TimeSheetDate</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">DATETIME</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  5.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DateOut</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">DATETIME</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  6.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">EmployeeID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  7.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">IsMainWorkPlace</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">BIT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DEFAULT</span>((<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">))
  8.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DepartmentUID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">UNIQUEIDENTIFIER</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  9.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">WorkPlaceUID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">UNIQUEIDENTIFIER</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  10.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TeamUID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">UNIQUEIDENTIFIER</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  11.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">WorkShiftCD</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>) COLLATE Cyrillic_General_CI_AS <span style="color: #0000ff">NULL</span><span style="color: #000000">
  12.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">WorkHours</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">REAL</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  13.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">AbsenceCode</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">25</span>) COLLATE Cyrillic_General_CI_AS <span style="color: #0000ff">NULL</span><span style="color: #000000">
  14.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">PaymentType</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">2</span>) COLLATE Cyrillic_General_CI_AS <span style="color: #0000ff">NULL</span><span style="color: #000000">
  15.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">CategoryID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">INT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  16.     , </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Year</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">AS</span> (<span style="color: #ff00ff">datepart</span>(<span style="color: #ff00ff">year</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TimeSheetDate</span><span style="color: #ff0000">]</span><span style="color: #000000">))
  17.     , </span><span style="color: #0000ff">CONSTRAINT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PK_WorkOut</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> (<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOutID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ASC</span><span style="color: #000000">)
  18. )
  19. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOut</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">WITH</span> <span style="color: #0000ff">CHECK</span> <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">FK_WorkOut_Employee_EmployeeID</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(<span style="color: #ff0000">[</span><span style="color: #ff0000">EmployeeID</span><span style="color: #ff0000">]</span>) <span style="color: #0000ff">REFERENCES</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">Employee</span><span style="color: #ff0000">]</span> (<span style="color: #ff0000">[</span><span style="color: #ff0000">EmployeeID</span><span style="color: #ff0000">]</span><span style="color: #000000">)
  20. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOut</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">CHECK</span> <span style="color: #0000ff">CONSTRAINT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">FK_WorkOut_Employee_EmployeeID</span><span style="color: #ff0000">]</span>
  21. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">NONCLUSTERED</span> <span style="color: #0000ff">INDEX</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">IX_WorkOut_WorkShiftCD_AbsenceCode</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ON</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOut</span><span style="color: #ff0000">]</span> (<span style="color: #ff0000">[</span><span style="color: #ff0000">WorkShiftCD</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ASC</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">AbsenceCode</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ASC</span><span style="color: #000000">)
  22. INCLUDE (</span><span style="color: #ff0000">[</span><span style="color: #ff0000">WorkOutID</span><span style="color: #ff0000">]</span>, <span style="color: #ff0000">[</span><span style="color: #ff0000">WorkHours</span><span style="color: #ff0000">]</span>)

如果将中间的SQL文稍加改造，则可以做成批量Create　SQL的语句。

你也可以参考：http://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query#comment24343659_706664

 

批量生成表Create　SQL　示例　Generate SQL Create Scripts for existing tables with Query

标签：identity   row   check   font   question   comm   init   执行   time   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！