动漫人物
这是我想出来的剧本。它处理标识列、默认值和主键。它不处理外键、索引、触发器或任何其他聪明的东西。它在SQLServer 2000、2005和2008上工作。declare @schema varchar(100), @table varchar(100)set @schema = 'dbo' -- set schema name hereset @table = 'MyTable' -- set table name heredeclare @sql table(s varchar(1000), id int identity)-- create statementinsert into @sql(s) values ('create table [' + @table + '] (')-- column listinsert 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 keydeclare @pkname varchar(100)select @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTSwhere 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 (' )')endelse begin -- remove trailing comma update @sql set s=left(s,len(s)-1) where id=@@identityend-- closing bracketinsert into @sql(s) values( ')' )-- result!select s from @sql order by id