在SQL Server中，如何为给定的表生成一个CREATE TABLE语句？

有人在论坛上发表了一个问题，询问如何在SQL Server中为给定的表生成CREATE TABLE语句。他想要将表结构脚本化，用于版本控制。

下面是一个提供的解决方法，可以生成CREATE TABLE语句：

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when UPPER(IS_NULLABLE) = 'NO' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 
     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

这个解决方法可以在SQL Server 2005及以上版本中使用。它使用了CROSS APPLY语句来支持新的SQL 2005数据类型，并保留了主键名称。它通过查询sysobjects和information_schema系统视图来获取表的结构信息，并使用FOR XML PATH('')将结果连接成一个字符串。最后，它使用CASE语句来判断是否有主键，并生成相应的ALTER TABLE语句。

这个解决方法在论坛上收到了积极的回应，有人称其为“太棒了”，并表示已经寻找了很长时间的类似解决方法。

在使用过程中，一些用户提出了一些改进意见和问题。有人指出numeric_precision_radix应该改为numeric_precision，另一人指出输出中有一个多余的逗号，提供了修改代码的建议。还某些情况下这个查询不会显示分区信息。

这个解决方法为在SQL Server中生成CREATE TABLE语句提供了一种有效的方法，并且在论坛上得到了广泛的认可和讨论。通过对代码进行一些修改，可以进一步完善和定制生成的CREATE TABLE语句。

在SQL Server中，如果我想为给定的表生成一个CREATE TABLE语句，我该怎么做呢？这个问题的出现的原因是因为需要将一个已有的表的结构生成一个CREATE TABLE语句，以便在其他地方重新创建该表。下面是一个处理这个问题的脚本，它可以处理Identity列、默认值和主键，但不能处理外键、索引、触发器或其他复杂的内容。这个脚本适用于SQL Server 2000、2005和2008。

declare @schema varchar(100), @table varchar(100)
set @schema = 'dbo' -- 设置模式名称
set @table = 'MyTable' -- 设置表名称
declare @create_table table(s varchar(1000), id int identity)
-- 创建语句
insert into @create_table (s) values ('create table [' + @schema + '].[' + @table + '] (')
-- 列列表
insert into @create_table (s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
 from INFORMATION_SCHEMA.COLUMNS where table_name = @table AND table_schema = @schema
 order by ordinal_position
-- 主键
declare @pk_name varchar(100)
select @pk_name = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = @table and constraint_type='PRIMARY KEY'
if (@pk_name is not null) begin
    insert into @create_table (s) values('  PRIMARY KEY (')
    insert into @create_table (s)
        select '   ['+COLUMN_NAME+'],' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        where constraint_name = @pk_name
        order by ordinal_position
    -- 删除尾部逗号
    update @create_table set s=left(s,len(s)-1) where id=@pk_name
    insert into @create_table (s) values ('  )')
end
else begin
    -- 删除尾部逗号
    update @create_table set s=left(s,len(s)-1) where id=@pk_name
end
-- 闭合括号
insert into @create_table (s) values( ')' )
-- 结果！
select s from @create_table order by id

许多系统由于动态SQL的原因无法运行这个脚本。在您的本地机器上可能没有问题，但我想指出这一点。

什么是动态SQL？这个脚本生成SQL语句，而不是执行SQL语句。

希望能将这个脚本分解成几个步骤，以便可以通过一系列的查询在C++中编写。

如果模式不是'dbo'，你会丢失对模式的支持。而且，如果一个数据库中有相同的表名但不同的模式，就会出现问题。不过，我已经得到了我需要的结果，谢谢。

确定吗？`select cast(123456 as varchar)`对我来说返回的是`123456`。

啊，是的，对不起。在CAST语句中，默认长度为30，如果不指定长度。在数据定义或变量声明中，默认长度为1。我以为默认长度一直是1。糟糕=)。（删除无效的注释）

这不是一条单行语句。

那又怎样？谁说它必须是一行的？

在SQL Server中，如果我想为给定的表生成一个CREATE TABLE语句，该怎么办？

有一个Powershell脚本在msdb论坛中，可以为所有的表和相关对象生成脚本：

# Script all tables in a database
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
    | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') ''
$db = $s.Databases['']
$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.FileName = 'C:\Temp\.SQL'
foreach($item in $db.Tables) { $tablearray+=@($item) }
$scrp.Script($tablearray)
Write-Host "Scripting complete"

当您拥有使用SMO的权限时，它非常好用。但是我发现有很多系统没有这样的权限，所以纯粹使用INFORMATION_SCHEMA的方法有一些优势。