生成SQL脚本

Question

大家都知道可以通过：右键数据库名称->任务->生成脚本 生成数据库所有脚本（包括所有表的结构、存储过程）能不能通过命令（存储过程） 实现这一功能吗？或者是其他方法生成，要在.NET 下应用，对数据库中所有表的结构生成SQL语句！

Answer 1

将表数据生成SQL脚本的存储过程:
　　
　　CREATE PROCEDURE dbo.UspOutputData
　　@tablename sysname
　　AS
　　declare @column varchar(1000)
　　declare @columndata varchar(1000)
　　declare @sql varchar(4000)
　　declare @xtype tinyint
　　declare @name sysname
　　declare @objectId int
　　declare @objectname sysname
　　declare @ident int
　　
　　set nocount on
　　set @objectId=object_id(@tablename)
　　
　　if @objectId is null -- 判斷對象是否存在
　　begin
　　print 'The object not exists'
　　return
　　end
　　set @objectname=rtrim(object_name(@objectId))
　　
　　if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
　　begin
　　print 'object not in current database'
　　return
　　end
　　
　　if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table
　　begin
　　print 'The object is not table'
　　return
　　end
　　
　　select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
　　
　　if @ident is not null
　　print 'SET IDENTITY_INSERT '+@TableName+' ON'
　　
　　declare syscolumns_cursor cursor
　　
　　for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
　　
　　open syscolumns_cursor
　　set @column=''
　　set @columndata=''
　　fetch next from syscolumns_cursor into @name,@xtype
　　
　　while @@fetch_status < >-1
　　begin
　　if @@fetch_status < >-2
　　begin
　　if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
　　
　　begin
　　set @column=@column+case when len(@column)=0 then'' else ','end+@name
　　
　　set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
　　end
　　
　　+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
　　when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
　　when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
　　when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
　　when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
　　else @name end
　　
　　end
　　
　　end
　　
　　fetch next from syscolumns_cursor into @name,@xtype
　　
　　end
　　
　　close syscolumns_cursor
　　deallocate syscolumns_cursor
　　
　　set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
　　
　　print '--'+@sql
　　exec(@sql)
　　
　　if @ident is not null
　　print 'SET IDENTITY_INSERT '+@TableName+' OFF'
　　
　　GO
　　
　　exec UspOutputData 你的表名 【转自www.bitsCN.com】

Answer 2

--2005及以上倒可以这样查看所有的存储过程
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P'
--同样函数可以这样
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'FN'
--其他的类似

Answer 3

sql自带的scptxfr.exe工具专门用来生成全部的脚本

Answer 4

CREATE PROCEDURE dbo.UspOutputData
　　@tablename sysname
　　AS
提示有错误：
消息 102，级别 15，状态 1，第 2 行
'　' 附近有语法错误。

Answer 5

CREATE PROCEDURE dbo.UspOutputData
@tablename sysname
AS

Answer 6

http://blog.csdn.net/blueseasand/archive/2007/08/07/1729433.aspx

Answer 7

"将表数据生成SQL脚本的存储过程:"谢谢2楼，可是我要的不是数据，而是通过数据表的结构 得到SQL （用这个SQL可以创建一张一样的表）

Answer 8

--2000
select text 
from syscomments
where id=object_Id('p1') --存储过程名，省略表示所有
--2005
SELECT definition 
FROM sys.sql_modules JOIN sys.objects 
ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P'
and sys.objects.name='P1' --存储过程名，省略表示所有

Answer 9

  USE   [master]   
  GO   
    
  SET   ANSI_NULLS   ON   
  GO   
  SET   QUOTED_IDENTIFIER   ON   
  GO   
  /*|<PRE>   
  ****************************************************************************   
    软件名称:   May   Flower   Erp   
    版权所有:   (C)   2005-2006   May   Flower   ERP   开发组   
    对象名称:   sp_HelpForeign   
    对象作者:   lvqy   
    创建日期:   2005-12-04   
    修改记录:   
    功能描述:   获取当前数据库所有表的外键创建脚本   
  ----------------------------------------------------------------------------   
    参数列表:   无参数   
  ****************************************************************************   
  |</PRE>*/   
  CREATE   PROCEDURE   [dbo].[sp_HelpForeign]   
  AS   
      SET   NOCOUNT   ON   
      DECLARE   @ConstID   int,   @i   tinyint,   @keyCnt   tinyint,   @TempletSQL   nvarchar(400),   @SQLScript   nvarchar(500),   
                      @FColName   sysname,   @RColName   sysname,     
                      @ForeignLine   nvarchar(4000),   @ReferencesLine   nvarchar(4000),   @ReferencesAction   nvarchar(4000)   
      DECLARE   @ForeignkeyScript   table(ScriptLine   nvarchar(4000))   
      DECLARE   @ConstIDTable   table(ConstID   int)   
      SELECT   
          @FColName   =   '',   
          @RColName   =   '',   
          @TempletSQL   =     
              N'SELECT   @eFColName   =   ''[''   +   col_name(FkeyID,   Fkey%d)   +   '']'',   @eRColName   =   ''[''   +   col_name(RkeyID,   Rkey%d)   +   '']''   FROM   sys.sysreferences   WHERE   ConstID   =   @ConstID'   
    
      INSERT   INTO   @ConstIDTable   
          SELECT   ConstID   FROM   sys.sysreferences   ORDER   BY   FKeyID   
    
      WHILE   EXISTS(SELECT   *   FROM   @ConstIDTable)   
      BEGIN   
          SELECT   TOP   1   @ConstID   =   ConstID   FROM   @ConstIDTable   
          DELETE   FROM   @ConstIDTable   WHERE   ConstID   =   @ConstID   
    
          INSERT   INTO   @ForeignkeyScript   
              SELECT   'ALTER   TABLE   [dbo].['   +   object_name(FKeyID)   +   ']   WITH   CHECK'   
                  FROM   sys.sysreferences   
              WHERE   ConstID   =   @ConstID   
          INSERT   INTO   @ForeignkeyScript(ScriptLine)   VALUES('     ADD'   +   CHAR(13)   +   CHAR(10))   
    
          SELECT   @ForeignLine   =   '         CONSTRAINT   ['   +   object_name(a.ConstID)   +   ']   FOREIGN   KEY(',     
                        @ReferencesLine   =   'REFERENCES   [dbo].['   +   object_name(a.RKeyID)   +   ']   (',     
                        @ReferencesAction   =   CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''     
                                                                                                                                  WHEN   1   THEN   N'ON   DELETE   Cascade'   
                                                                                                                                  WHEN   2   THEN   N'ON   DELETE   SET   NULL'   
                                                                                                                                  WHEN   3   THEN   N'ON   DELETE   SET   DEFAULT'   
                                                                END   +   '   '   +   
                                                                CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''     
                                                                                                                                  WHEN   1   THEN   N'ON   UPDATE   Cascade'   
                                                                                                                                  WHEN   2   THEN   N'ON   UPDATE   SET   NULL'   
                                                                                                                                  WHEN   3   THEN   N'ON   UPDATE   SET   DEFAULT'   
                                                                END,   
                        @keyCnt   =   KeyCnt     
              FROM   sys.sysreferences   a   
                  LEFT   JOIN   sys.foreign_keys   b   ON   a.ConstID   =   b.Object_ID   
          WHERE   a.ConstID   =   @ConstID   
    
          --取字段   
          SET   @i   =   1   
          WHILE   @i   <=   @keyCnt   
          BEGIN   
              SET   @SQLScript   =   REPLACE(@TempletSQL,   '%d',   CONVERT(varchar,   @i))   
              EXEC   sp_executesql   @stmt   =   @SQLScript,   @params=   N'@eFColName   sysname   output,   @eRColName   sysname   output,   @ConstID   int',     
                                                    @eFColName   =   @FColName   output,   @eRColName   =   @RColName   output,   @ConstID   =   @ConstID   
              print   @SQLScript   
              SET   @ForeignLine   =   @ForeignLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @FColName   
              SET   @ReferencesLine   =   @ReferencesLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @RColName   
              SET   @i   =   @i   +   1   
          END   
    
          INSERT   INTO   @ForeignkeyScript(ScriptLine)   VALUES(@ForeignLine   +   ')   '   +   @ReferencesLine   +   ')')   
          IF   @ReferencesAction   <>   ''   
              INSERT   INTO   @ForeignkeyScript(ScriptLine)   VALUES(@ReferencesAction)   
          INSERT   INTO   @ForeignkeyScript(ScriptLine)   VALUES('GO')   
      END   
    
      SELECT   *   FROM   @ForeignkeyScript   
      SET   NOCOUNT   OFF   
  GO 

Answer 10

USE   [master]   
  GO 
  SET   ANSI_NULLS   ON   
  GO   
  SET   QUOTED_IDENTIFIER   ON   
  GO   
  /*|<PRE>   
  ****************************************************************************   
    软件名称:   May   Flower   Erp   
    版权所有:   (C)   2005-2006   May   Flower   ERP   开发组   
    对象名称:   sp_HelpTable   
    对象作者:   lvqy   
    创建日期:   2005-12-04   
    修改记录:   
    功能描述:   获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)   
  ----------------------------------------------------------------------------   
    参数列表:     
                  1:   @TableName   需要创建脚本的表的名称   
  ****************************************************************************   
  |</PRE>*/   
  CREATE   PROCEDURE   [dbo].[sp_HelpTable](@TableName   sysname)   
  AS   
      SET   NOCOUNT   ON   
      DECLARE   @ObjectID   int   
      DECLARE   @TableScript   table(Iden   int   IDENTITY(1,   1),   ScriptLine   nvarchar(4000))   
      SET   @ObjectID   =   object_id(@TableName)   
      IF   @ObjectID   IS   NULL   OR   OBJECTPROPERTY(@ObjectID,   'IsTable')   =   0   
      BEGIN   
          RAISERROR('指定的对象不是表对象',   16,   1)   
          RETURN   
      END     
      --获取表的创建脚本   
      --插入表头   
      INSERT   INTO   @TableScript(ScriptLine)   
          SELECT   N'CREATE   TABLE   ['   +   USER_NAME(OBJECTPROPERTY(@ObjectID,   N'OwnerId'))   +   N'].['   +   object_name(@ObjectID)   +   N']('   
    
      --插入字段   
      INSERT   INTO   @TableScript(ScriptLine)   
          SELECT   N'     ['   +   a.Name   +   N']   ['   +   b.name   +   N']'   +     
                        CASE   WHEN   c.Object_id   IS   NOT   NULL   THEN   N'   IDENTITY('   +   CONVERT(nvarchar,   c.seed_value)   +   N',   '   +   CONVERT(nvarchar,   c.increment_value)   +   N')'     
                                  ELSE   ''   END   +   
                        CASE   WHEN   b.xusertype   IN   (167,   175,   231,   239)   THEN   N'('+CONVERT(nvarchar,   a.prec)     +   N')'   
                                  WHEN   b.xusertype   in   (106,   108)                       THEN   N'('+CONVERT(nvarchar,   a.xprec)   +   N',   '   +   CONVERT(nvarchar,   a.xscale)   +   N')'   
                                  ELSE   ''   END   +   
                        CASE   a.isnullable   WHEN   1   THEN   N''   ELSE   N'   NOT'   END   +   N'   NULL'   +   
                        CASE   WHEN   d.Name   IS   NOT   NULL   THEN   N'   DEFAULT   '   +   d.Definition   ELSE   N''   END   +   
                        N','     
              FROM   sys.syscolumns   a   
                  LEFT   JOIN   sys.systypes   b   ON   a.xusertype   =   b.xusertype   
                  LEFT   JOIN   sys.identity_columns   c   ON   c.Object_id   =   a.ID   AND   c.Column_ID   =   a.ColID   
                  LEFT   JOIN   sys.default_constraints   d   ON   d.Parent_Object_ID   =   a.ID   AND   d.Parent_column_ID   =   a.ColID   
          WHERE   a.[ID]   =   @ObjectID   
          ORDER   BY   a.ColOrder   
    

Answer 11

      --插入主键和索引   
      DECLARE   @IndexID   int,   @IndexScript   nvarchar(4000)   
      DECLARE   IndexCursor   CURSOR   FOR     
          SELECT   b.Index_ID,   N'     CONSTRAINT   ['   +   a.Name   +   N']   '   +     
                        CASE   a.Type   WHEN   'PK'   THEN   N'PRIMARY   KEY   '   WHEN   'UQ'   THEN   N'UNIQUE   '   END   +     
                        CASE   b.Type   WHEN   1         THEN   N'CLUSTERED'       WHEN   2           THEN   N'NONCLUSTERED   '   END   +   N'('   
              FROM   sys.key_constraints   a   
                  LEFT   JOIN   sys.indexes   b   ON   b.Object_ID   =   a.Parent_Object_ID   AND   b.index_id   =   a.unique_index_id   
          WHERE   a.Parent_Object_ID   =   @ObjectID   
      OPEN   IndexCursor   
      FETCH   NEXT   FROM   IndexCursor   INTO   @IndexID,   @IndexScript   
      WHILE   @@FETCH_STATUS   =   0   
      BEGIN   
          SELECT   @IndexScript   =   @IndexScript   +   N'['   +   INDEX_COL(object_name(@ObjectID),   2   ,   1)   +   N'],'   
              FROM   sys.index_columns     
          WHERE   Object_ID   =   @ObjectID   
              AND   Index_ID   =   2   
            
          SET   @IndexScript   =   LEFT(@IndexScript,   LEN(@IndexScript)   -1)   +   N'),'   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(@IndexScript)   
    
          FETCH   NEXT   FROM   IndexCursor   INTO   @IndexID,   @IndexScript   
      END   
      CLOSE   IndexCursor   
      DEALLOCATE   IndexCursor   
    
      --除去最后一个,号   
      UPDATE   @TableScript     
          SET   ScriptLine   =   LEFT(ScriptLine,   LEN(ScriptLine)   -   1)   
      WHERE   Iden   =   (SELECT   MAX(Iden)   FROM   @TableScript)   
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N')')   
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')   
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'')   
    --获取表备注   
      DECLARE   @PropScript   nvarchar(4000)   
      INSERT   INTO   @TableScript(ScriptLine)   
          SELECT   N'EXEC   sys.sp_addextendedproperty   @name=N'''   +   a.Name   +   N''',   @value=N'''   +   CONVERT(nvarchar,   a.Value)   +   N'''   ,@level0type=N''SCHEMA'',   @level0name=N'''   +   
                        USER_NAME(OBJECTPROPERTY(a.major_Id,   N'OwnerId'))   +   N''',   @level1type=N''TABLE'',   @level1name=N'''   +   b.Name   +   N''''   
              FROM   sys.extended_properties   a   
                  LEFT   JOIN   sys.objects   b   ON   b.[Object_ID]   =   a.major_Id   
          WHERE   a.major_Id   =   @ObjectID   
              AND   Minor_ID   =   0   
      INSERT   INTO   @TableScript(ScriptLine)   VALUES('GO')   
    
      DECLARE   PropCursor   CURSOR   FOR     
          SELECT   N'EXEC   sys.sp_addextendedproperty   @name=N'''   +   a.Name   +   N''',   @value=N'''   +   CONVERT(nvarchar,   a.Value)   +   N'''   ,@level0type=N''SCHEMA'',   @level0name=N'''   +   
                        USER_NAME(OBJECTPROPERTY(a.major_Id,   N'OwnerId'))   +   N''',   @level1type=N''TABLE'',   @level1name=N'''   +   b.Name   +   N''''   +   
                        N',   @level2type=N''COLUMN'',   @level2name=N'''   +   c.[Name]   +   ''''   
              FROM   sys.extended_properties   a   
                  LEFT   JOIN   sys.objects   b   ON   b.[Object_ID]   =   a.major_Id   
                  LEFT   JOIN   sys.syscolumns   c   ON   c.[ID]   =   a.major_Id   AND   c.ColID   =   a.Minor_ID   
          WHERE   a.major_Id   =   @ObjectID   
              AND   Minor_ID   <>   0   
      OPEN   PropCursor   
      FETCH   NEXT   FROM   PropCursor   INTO   @PropScript   
      WHILE   @@FETCH_STATUS   =   0   
      BEGIN   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(@PropScript)   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')   
    
          FETCH   NEXT   FROM   PropCursor   INTO   @PropScript   
      END   
      CLOSE   PropCursor   
      DEALLOCATE   PropCursor   
      INSERT   INTO   @TableScript(ScriptLine)   VALUES('')   
    
      --获取表外键   
      DECLARE   @ConstID   int,   @i   tinyint,   @keyCnt   tinyint,   @TempletSQL   nvarchar(400),   @SQLScript   nvarchar(500),   
                      @FColName   sysname,   @RColName   sysname,   
                      @ForeignLine   nvarchar(4000),   @ReferencesLine   nvarchar(4000),   @ReferencesAction   nvarchar(4000)   
      DECLARE   @ConstIDTable   table(ConstID   int)   
      SELECT   
          @FColName   =   '',   
          @RColName   =   '',   
          @TempletSQL   =     
              N'SELECT   @eFColName   =   ''[''   +   col_name(FkeyID,   Fkey%d)   +   '']'',   @eRColName   =   ''[''   +   col_name(RkeyID,   Rkey%d)   +   '']''   FROM   sys.sysreferences   WHERE   ConstID   =   @ConstID'   
    
      INSERT   INTO   @ConstIDTable   
          SELECT   ConstID   FROM   sys.sysreferences   WHERE   FKeyID   =   @ObjectID   OR   RKeyID   =   @ObjectID   ORDER   BY   FKeyID   
    
      WHILE   EXISTS(SELECT   *   FROM   @ConstIDTable)   
      BEGIN   
          SELECT   TOP   1   @ConstID   =   ConstID   FROM   @ConstIDTable   
          DELETE   FROM   @ConstIDTable   WHERE   ConstID   =   @ConstID   
    
          INSERT   INTO   @TableScript   
              SELECT   N'--ALTER   TABLE   [dbo].['   +   object_name(FKeyID)   +   ']   WITH   CHECK'   
                  FROM   sys.sysreferences   
              WHERE   ConstID   =   @ConstID   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES('--     ADD'   +   CHAR(13)   +   CHAR(10))   
    
          SELECT   @ForeignLine   =   N'--         CONSTRAINT   ['   +   object_name(ConstID)   +   ']   FOREIGN   KEY(',     
                        @ReferencesLine   =   N'REFERENCES   [dbo].['   +   object_name(RKeyID)   +   ']   (',     
                        @ReferencesAction   =   CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''     
                                                                                                                                  WHEN   1   THEN   N'ON   DELETE   Cascade'   
                                                                                                                                  WHEN   2   THEN   N'ON   DELETE   SET   NULL'   
                                                                                                                                  WHEN   3   THEN   N'ON   DELETE   SET   DEFAULT'   
                                                                END   +   '   '   +   
                                                                CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''     
                                                                                                                                  WHEN   1   THEN   N'ON   UPDATE   Cascade'   
                                                                                                                                  WHEN   2   THEN   N'ON   UPDATE   SET   NULL'   
                                                                                                                                  WHEN   3   THEN   N'ON   UPDATE   SET   DEFAULT'   
                                                                END,   
                        @keyCnt   =   KeyCnt     
              FROM   sys.sysreferences   a   
                  LEFT   JOIN   sys.foreign_keys   b   ON   a.ConstID   =   b.Object_ID   
          WHERE   a.ConstID   =   @ConstID   
            
          --取字段   
          SET   @i   =   1   
          WHILE   @i   <=   @keyCnt   
          BEGIN   
              SET   @SQLScript   =   REPLACE(@TempletSQL,   '%d',   CONVERT(nvarchar,   @i))   
              EXEC   sp_executesql   @stmt   =   @SQLScript,   @params=   N'@eFColName   sysname   output,   @eRColName   sysname   output,   @ConstID   int',     
                                                    @eFColName   =   @FColName   output,   @eRColName   =   @RColName   output,   @ConstID   =   @ConstID   
              print   @SQLScript   
              SET   @ForeignLine   =   @ForeignLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @FColName   
              SET   @ReferencesLine   =   @ReferencesLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @RColName   
              SET   @i   =   @i   +   1   
          END   
    
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(@ForeignLine   +   N')   '   +   @ReferencesLine   +   N')')   
          IF   @ReferencesAction   <>   ''   
              INSERT   INTO   @TableScript(ScriptLine)   VALUES(@ReferencesAction)   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')   
      END   
      --返回表的创建脚本   
      SELECT   ScriptLine   FROM   @TableScript   
      SET   NOCOUNT   OFF