我花了很多时间来解决这个问题，因此，本着本文的精神，我将其发布在这里，因为我认为这可能对其他人有用。

如果有人有更好的脚本或者要添加的内容，请发布。

编辑：是的，我知道如何在Management Studio中执行此操作，但是我需要能够从另一个应用程序中执行此操作。

解决方案

回答

这是我想到的脚本。它处理"标识"列，默认值和主键。它不处理外键，索引，触发器或者任何其他聪明的东西。它适用于SQLServer 2000、2005和2008.

declare @table varchar(100)
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
 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

回答

感谢@Blorgbeard分享了他的脚本。我一定会在需要时为其添加书签。

是的，我们可以在表上"右键单击"并为" CREATE TABLE"脚本编写脚本，但是：

• 一个脚本将包含大量的碎片(对扩展属性感兴趣吗？)
• 如果架构中有200多个表，那么将需要半天的时间来手动编写很多脚本。

将此脚本转换为存储过程，并与包装器脚本结合使用，我们将有一个很好的自动化方法将表设计转储到源代码控制等中。

无论如何，其余的数据库代码(SP，FK索引，触发器等)都将在源代码控制下；)

回答

我在INFORMATION_SCHEMA.COLUMNS视图中注意到的一些东西，CHARACTER_MAXIMUM_LENGTH的大小为2147483647(2 ^ 31-1)，用于图像和文本等字段类型。 ntext是2 ^ 30-1(是双字节unicode和全部)。

该大小包含在该查询的输出中，但是对于CREATE语句中的这些数据类型无效(它们根本不应该具有最大大小值)。因此，除非手动纠正由此产生的结果，否则在这些数据类型下CREATE脚本将无法工作。

我想可以解决该问题的脚本，但这超出了我的SQL功能。

回答

如果我们正在使用Management Studio并打开了查询分析器窗口，则可以将表名拖到查询分析器窗口中，然后...宾果游戏！我们将获得表脚本。
我没有在SQL2008中尝试过

回答

我已经修改了上面的版本，可以在所有表​​上运行，并支持新的SQL 2005数据类型。它还保留主键名称。仅适用于SQL 2005(使用交叉应用)。

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 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')

更新：添加了对XML数据类型的处理

更新2：修复以下情况：1)有多个具有相同名称但具有不同架构的表，2)有多个具有PK约束且具有相同名称的表

回答

如果要从中生成脚本的应用程序是.NET应用程序，则可能需要研究使用SMO(SQL管理对象)。请参考此SQL Team链接，了解如何使用SMO编写对象脚本。

回答

-或者我们可以创建存储过程...首先使用ID创建

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROC [dbo].[procUtils_InsertGeneratorWithId]    
(    
@domain_user varchar(50),    
@tableName varchar(100)    
)     

as    

--Declare a cursor to retrieve column specific information for the specified table    
DECLARE cursCol CURSOR FAST_FORWARD FOR     
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName    
OPEN cursCol    
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement    
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement    
DECLARE @dataType nvarchar(1000) --data types returned for respective columns    
DECLARE @IDENTITY_STRING nvarchar ( 100 )    
SET @IDENTITY_STRING = ' '     
select  @IDENTITY_STRING    
SET @string='INSERT '+@tableName+'('    
SET @stringData=''    

DECLARE @colName nvarchar(50)    

FETCH NEXT FROM cursCol INTO @colName,@dataType    

IF @@fetch_status<>0    
 begin    
 print 'Table '+@tableName+' not found, processing skipped.'    
 close curscol    
 deallocate curscol    
 return    
END    

WHILE @@FETCH_STATUS=0    
BEGIN    
IF @dataType in ('varchar','char','nchar','nvarchar')    
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'    
END    
ELSE    
if @dataType in ('text','ntext') --if the datatype is text or something else     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'    
END    
ELSE    
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly    
BEGIN    
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
END    
ELSE     
IF @dataType='datetime'    
BEGIN    
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'    
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'    
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations    
END    
ELSE     
IF @dataType='image'     
BEGIN    
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'    
END    
ELSE --presuming the data type is int,bit,numeric,decimal     
BEGIN    
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'    
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'    
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'    
END    

SET @string=@string+@colName+','    

FETCH NEXT FROM cursCol INTO @colName,@dataType    
END    
DECLARE @Query nvarchar(4000)    

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName    
exec sp_executesql @query    
--select @query    

CLOSE cursCol    
DEALLOCATE cursCol    

  /*
USAGE

*/

GO

-和第二次没有iD插入

USE [db]
GO

/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[procUtils_InsertGenerator]        
(        
@domain_user varchar(50),        
@tableName varchar(100)        
)         

as        

--Declare a cursor to retrieve column specific information for the specified table        
DECLARE cursCol CURSOR FAST_FORWARD FOR         

-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName        
/* NEW     
SELECT c.name , sc.data_type  FROM sys.extended_properties AS ep                   
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id                   
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id                   
= c.column_id                   
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and                   
c.name = sc.column_name                   
WHERE t.name = @tableName and c.is_identity=0      
  */      

select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"      
  from sys.columns c          
  join sys.systypes s on (s.xtype = c.system_type_id)          
  where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')          
   AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName  ) and c.is_identity=0 and s.name not like 'sysname'  

OPEN cursCol        
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement        
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement        
DECLARE @dataType nvarchar(1000) --data types returned for respective columns        
DECLARE @IDENTITY_STRING nvarchar ( 100 )        
SET @IDENTITY_STRING = ' '         
select  @IDENTITY_STRING        
SET @string='INSERT '+@tableName+'('        
SET @stringData=''        

DECLARE @colName nvarchar(50)        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        

IF @@fetch_status<>0        
 begin        
 print 'Table '+@tableName+' not found, processing skipped.'        
 close curscol        
 deallocate curscol        
 return        
END        

WHILE @@FETCH_STATUS=0        
BEGIN        
IF @dataType in ('varchar','char','nchar','nvarchar')        
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'        
END        
ELSE        
if @dataType in ('text','ntext') --if the datatype is text or something else         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'        
END        
ELSE        
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly        
BEGIN        
 SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
END        
ELSE         
IF @dataType='datetime'        
BEGIN        
 --SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'        
 --SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
 --SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'        
 SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
  --                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations        
END        
ELSE         
IF @dataType='image'         
BEGIN        
 SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'        
END        
ELSE --presuming the data type is int,bit,numeric,decimal         
BEGIN        
 --SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'        
 --SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'        
 SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'        
END        

SET @string=@string+@colName+','        

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType        
END        
DECLARE @Query nvarchar(4000)        

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName        
exec sp_executesql @query        
--select @query       

CLOSE cursCol        
DEALLOCATE cursCol        

  /*      

use poc     
go    

DECLARE @RC int      
DECLARE @domain_user varchar(50)      
DECLARE @tableName varchar(100)      

-- TODO: Set parameter values here.      
set @domain_user='yorgeorg'      
set @tableName = 'tbGui_WizardTabButtonAreas'      

EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]       
   @domain_user      
  ,@tableName      

*/
GO