复制存储过程

2015/11/30 43201

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
USE master
 
go
 
IF EXISTS (SELECT *
           FROM   dbo.sysobjects
           WHERE  id = Object_id(N'[dbo].[sp_copyProce]')
                  AND Objectproperty(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[sp_copyProce]
 
go
 
CREATE PROC Sp_copyproce @s_dbname SYSNAME,--要复制存储过程的源数据库名
                         @d_dbname SYSNAME --目标数据库名
AS
    SET nocount ON
 
    IF Db_id(@s_dbname) IS NULL
      BEGIN
          RAISERROR('数据库"%s"不存在',1,16,@s_dbname)
 
          RETURN
      END
 
    IF Db_id(@d_dbname) IS NULL
      BEGIN
          RAISERROR('数据库"%s"不存在',1,16,@d_dbname)
 
          RETURN
      END
 
    SELECT @s_dbname = '[' + Replace(@s_dbname, ']', ']]') + ']',
           @d_dbname = '[' + Replace(@d_dbname, ']', ']]') + ']'
 
    --复制存储过程信息到临时表
    CREATE TABLE #sys_syscomments_bak
      (
         name   SYSNAME,
         xtype  CHAR(2),
         number SMALLINT,
         colid  SMALLINT,
         status SMALLINT,
         ctext  TEXT
      )
 
    --select o.name,o.xtype,c.number,c.colid,c.status,c.ctext into #sys_syscomments_bak
    --from dbo.syscomments c,dbo.sysobjects o where 1=2
    EXEC(
'insert #sys_syscomments_bak(name,xtype,number,colid,status,ctext) select o.name,o.xtype,c.number,c.colid,c.status,cast(c.ctext as varchar(8000)) from '
    +@s_dbname+'.dbo.syscomments c,'+@s_dbname+'.dbo.sysobjects o where c.id=o.id      and o.status>=0      and o.xtype=''P''      and not exists(          select * from '+
    @d_dbname+'.dbo.sysobjects where name=o.name)')
 
    --创建存储过程
    DECLARE tb CURSOR local FOR
      SELECT 'use ' + @d_dbname
             + ' exec(''create proc dbo.['
             + Replace(name, N']', N']]')
             + '] as --'')  exec sp_recompile ['
             + Replace(name, N']', N']]') + ']'
      FROM   #sys_syscomments_bak
    DECLARE @s NVARCHAR(4000)
 
    OPEN tb
 
    FETCH tb INTO @s
 
    WHILE @@fetch_status = 0
      BEGIN
          EXEC(@s)
 
          FETCH tb INTO @s
      END
 
    CLOSE tb
 
    DEALLOCATE tb
 
    --复制存储过程结构
    EXEC Sp_configure
      'allow updates',
      1 --reconfigure with override
    BEGIN TRAN
 
    EXEC('delete c from '+@d_dbname+'.dbo.syscomments c,'+@d_dbname+
    '.dbo.sysobjects o,#sys_syscomments_bak ob      where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype      insert '+@d_dbname+'.dbo.syscomments([id],[number],[colid],[status],[ctext])      select o.[id],ob.[number],ob.[colid],ob.[status],cast(cast(ob.[ctext] as varchar(8000)) as varbinary(8000))      from '+@d_dbname+
    '.dbo.sysobjects o,#sys_syscomments_bak ob      where o.name=ob.name and o.xtype=ob.xtype')
 
    COMMIT TRAN
 
    EXEC Sp_configure
      'allow updates',
      0 --reconfigure with override
 
go