SqlServer导库语句 

 1 GO
 2 /****** 对象:  StoredProcedure [dbo].[sp_ExportDatabase]    脚本日期: 07/18/2013 12:37:26 ******/
 3 SET ANSI_NULLS ON
 4 GO
 5 SET QUOTED_IDENTIFIER ON
 6 GO
 7 ----该存储过程建立在新帐套中，并在新账套中执行
 8 
 9 if Exists(select * from sysobjects where name=N'sp_ExportDatabase' And Xtype='P')
10     Drop PROCEDURE [sp_ExportDatabase]
11 Go
12 
13 CREATE PROCEDURE [dbo].[sp_ExportDatabase] (
14     @SourceDB varchar(100)
15 ) ----创建存储过程 sp_ExportDatabase
16 AS
17 Begin
18       Set NoCount On
19     Declare @Utb sysname     ------用户表名
20     Declare @ColName sysname   ------列名
21     Declare @tid int       ------用户表的ID
22     Declare @sql nvarchar(MAX)------存放拼出的sql
23     Declare @len int
24     --定义游标取回用户建立的表
25     sELECT @SourceDB=@SourceDB+'.'
26     Declare Ctb Cursor For
27     Select name,id From sysobjects
28     Where xtype ='U' ----如果在导库过程中因某表存在错误而导致导库过程停止 ，则可以尝试修复此表。
29               ----如果此表不是很重要、或是此表无法修复，则可以在此加入条件
30     --And name in (tablename1,tablename2,……)
31     ------tablename1,tablename2 表示不能修复的表的名字
32     Order by name
33     Open Ctb
34     Fetch Ctb Into @Utb,@tid
35     While (@@FETCH_STATUS=0)
36     Begin   ----禁用当前数据库中所有表的约束、触发器
37         Select @Utb='Dbo.'+@Utb
38         Select @SQL='Alter Table '+@Utb+' Disable Trigger All; '+ ' ALTER TABLE '+ @Utb +' NOCHECK CONSTRAINT All; '
39         exec ( @SQL)
40         Fetch Ctb Into @Utb,@tid
41     End  
42     close ctb
43     Open Ctb
44         Fetch Ctb Into @Utb,@tid
45         While (@@FETCH_STATUS=0)
46         Begin
47           Select @Utb='Dbo.'+@Utb
48                 
49           exec (' Delete ' + @Utb)
50               
51               Set @sql='['
52               Declare Clu Cursor For Select name From syscolumns Where id=@tid And iscomputed=0 and xtype<>189
53               Open Clu
54               Fetch Clu Into @ColName
55               While (@@FETCH_STATUS=0)
56               Begin     ----把列名以逗号隔开，拼成字符串              
57                   Set @sql=@sql+ @ColName + '],['
58                   Fetch Clu Into @ColName
59               End
60               Close Clu
61               DeAllocate Clu
62               ----构造字符串        
63               Set @len=Len(@sql)
64               If @len>0
65               Begin   ----把源数据库中的表导入到当前数据库中
66                   Select @sql=left(@sql,@len-2)
67                   Set @sql='Insert Into '+ @Utb + ' ('+@sql+') '+' Select '+@sql+' From ' + @SourceDB+ @Utb
68                   print 'Importing Table : ' +@utb+'...'
69                   If Exists (Select name From syscolumns Where id=@tid and status=0x80)
70                       begin
71                       Select @SQl='Set IDENTITY_INSERT '+ @Utb + ' ON' + ' Delete ' + @Utb+' '+@sql
72                       print @sql
73                       end
74                   Else
75                       Select @SQl=@sql
76         
77                   Exec ( @sql)
78                   If Exists (Select name From syscolumns Where id=@tid and status=0x80)
79                       Exec( 'Set IDENTITY_INSERT '+ @Utb + ' Off')
80                   print 'Importing Table : ' +@utb+' complete'
81               End
82                   
83               Fetch Next From Ctb Into @Utb,@tid
84         End
85     Close Ctb
86     Open Ctb
87     Fetch Ctb Into @Utb,@tid
88     While (@@FETCH_STATUS=0)
89     Begin   ----启用当前数据库中所有表的约束、触发器
90         Select @Utb='Dbo.'+@Utb
91         select @sql='Alter Table '+@Utb+' Enable Trigger All '+ ' ALTER TABLE '+ @Utb +' CHECK CONSTRAINT All '
92         Exec sp_executesql @sql
93         Fetch Ctb Into @Utb,@tid
94     End
95     close ctb
96     DeAllocate Ctb
97 print 'Import database complete!'
98     return 0
99 End

使用方法

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

 

posted @ 2016-01-02 19:43  dotqi  阅读(297)  评论(0)  编辑  收藏  举报