SqlServer导库语句

 GO
 /****** 对象:  StoredProcedure [dbo].[sp_ExportDatabase]    脚本日期: 07/18/2013 12:37:26 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 ----该存储过程建立在新帐套中，并在新账套中执行

 if Exists(select * from sysobjects where name=N'sp_ExportDatabase' And Xtype='P')
     Drop PROCEDURE [sp_ExportDatabase]
 Go

 CREATE PROCEDURE [dbo].[sp_ExportDatabase] (
     )
 ) ----创建存储过程 sp_ExportDatabase
 AS
 Begin
       Set NoCount On
     Declare @Utb sysname     ------用户表名
     Declare @ColName sysname   ------列名
     Declare @tid int       ------用户表的ID
     Declare @sql nvarchar(MAX)------存放拼出的sql
     Declare @len int
     --定义游标取回用户建立的表
     sELECT @SourceDB=@SourceDB+'.'
     Declare Ctb Cursor For
     Select name,id From sysobjects
     Where xtype ='U' ----如果在导库过程中因某表存在错误而导致导库过程停止 ，则可以尝试修复此表。
               ----如果此表不是很重要、或是此表无法修复，则可以在此加入条件
     --And name in (tablename1,tablename2,……)
     ------tablename1,tablename2 表示不能修复的表的名字
     Order by name
     Open Ctb
     Fetch Ctb Into @Utb,@tid
     )
     Begin   ----禁用当前数据库中所有表的约束、触发器
         Select @Utb='Dbo.'+@Utb
         Select @SQL='Alter Table '+@Utb+' Disable Trigger All; '+ ' ALTER TABLE '+ @Utb +' NOCHECK CONSTRAINT All; '
         exec ( @SQL)
         Fetch Ctb Into @Utb,@tid
     End
     close ctb
     Open Ctb
         Fetch Ctb Into @Utb,@tid
         )
         Begin
           Select @Utb='Dbo.'+@Utb

           exec (' Delete ' + @Utb)

               Set @sql='['

               Open Clu
               Fetch Clu Into @ColName
               )
               Begin     ----把列名以逗号隔开，拼成字符串
                   Set @sql=@sql+ @ColName + '],['
                   Fetch Clu Into @ColName
               End
               Close Clu
               DeAllocate Clu
               ----构造字符串
               Set @len=Len(@sql)

               Begin   ----把源数据库中的表导入到当前数据库中
                   )
                   Set @sql='Insert Into '+ @Utb + ' ('+@sql+') '+' Select '+@sql+' From ' + @SourceDB+ @Utb
                   print 'Importing Table : ' +@utb+'...'
                   If Exists (Select name From syscolumns Where id=@tid and status=0x80)
                       begin
                       Select @SQl='Set IDENTITY_INSERT '+ @Utb + ' ON' + ' Delete ' + @Utb+' '+@sql
                       print @sql
                       end
                   Else
                       Select @SQl=@sql

                   Exec ( @sql)
                   If Exists (Select name From syscolumns Where id=@tid and status=0x80)
                       Exec( 'Set IDENTITY_INSERT '+ @Utb + ' Off')
                   print 'Importing Table : ' +@utb+' complete'
               End

               Fetch Next From Ctb Into @Utb,@tid
         End
     Close Ctb
     Open Ctb
     Fetch Ctb Into @Utb,@tid
     )
     Begin   ----启用当前数据库中所有表的约束、触发器
         Select @Utb='Dbo.'+@Utb
         select @sql='Alter Table '+@Utb+' Enable Trigger All '+ ' ALTER TABLE '+ @Utb +' CHECK CONSTRAINT All '
         Exec sp_executesql @sql
         Fetch Ctb Into @Utb,@tid
     End
     close ctb
     DeAllocate Ctb
 print 'Import database complete!'

 End

使用方法

Exec sp_ExportDatabase mytest   ----mytest表示源数据实体的名称

秒客网

SqlServer导库语句

相关文章