SQL Server数据库下教你如何做导库SQL(2)

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

Create PROCEDURE [sp_ExportDatabase] (
    @SourceDB varchar(100)
) ----创建存储过程 sp_ExportDatabase
AS
Begin
      Set NoCount On

    Declare @Utb sysname     ------用户表名
    Declare @ColName sysname   ------列名
    Declare @tid int       ------用户表的ID
    Declare @sql nvarchar(3000)------存放拼出的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
    While (@@FETCH_STATUS=0)
    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
        While (@@FETCH_STATUS=0)
        Begin
          Select @Utb='Dbo.'+@Utb
                
          exec (' Delete ' + @Utb)
              
              Set @sql=''
              Declare Clu Cursor For Select name From syscolumns Where id=@tid And iscomputed=0 and xtype<>189
              Open Clu
              Fetch Clu Into @ColName
              While (@@FETCH_STATUS=0)
              Begin     ----把列名以逗号隔开，拼成字符串               
                  Set @sql=@sql+ @ColName + ',' 
                  Fetch Clu Into @ColName
              End
              Close Clu
              DeAllocate Clu

              ----构造字符串         
              Set @len=Len(@sql)
              If @len>0 
              Begin   ----把源数据库中的表导入到当前数据库中
                  Select @sql=left(@sql,@len-1)
                  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
    While (@@FETCH_STATUS=0)
    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!'
    return 0
End
Go

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