SqlServer导库语句
时间：2021-07-01 10:21:17
帮助过：3人阅读
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表示源数据实体的名称
SqlServer导库语句
标签：
