在SQLServer中，如何为给定表生成CREATETABLE语句？

Question

在SQLServer中，如何为给定表生成CREATETABLE语句？我花了很多时间想出解决这个问题的办法，所以本着这个职位，我把它张贴在这里，因为我认为它可能对其他人有用。如果有人有一个更好的脚本，或任何添加，请张贴它。编辑：是的，伙计们，我知道如何在ManagementStudio中这样做-但我需要能够在另一个应用程序中做到这一点。

Answer 1

这是我想出来的剧本。它处理标识列、默认值和主键。它不处理外键、索引、触发器或任何其他聪明的东西。它在SQLServer 2000、2005和2008上工作。

declare @schema varchar(100), @table varchar(100)

set @schema = 'dbo' -- set schema name here

set @table = 'MyTable' -- set table name here

declare @sql table(s varchar(1000), id int identity)

-- create statement

insert into  @sql(s) values ('create table [' + @table + '] (')

-- column list

insert into @sql(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

-- primary key

declare @pkname varchar(100)

select @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin

    insert into @sql(s) values('  PRIMARY KEY (')

    insert into @sql(s)

        select '   ['+COLUMN_NAME+'],' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

        where constraint_name = @pkname

        order by ordinal_position

    -- remove trailing comma

    update @sql set s=left(s,len(s)-1) where id=@@identity

    insert into @sql(s) values ('  )')

end

else begin

    -- remove trailing comma

    update @sql set s=left(s,len(s)-1) where id=@@identity

end

-- closing bracket

insert into @sql(s) values( ')' )

-- result!

select s from @sql order by id

Answer 2

中隐藏了一个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') '<Servername>'

$db = $s.Databases['<Database>']

$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\<Database>.SQL'

foreach($item in $db.Tables) { $tablearray+=@($item) }

$scrp.Script($tablearray)

Write-Host "Scripting complete"