当前位置:Gxlcms > mysql > 复制存储过程

复制存储过程

时间:2021-07-01 10:21:17 帮助过:37人阅读

SQL语句复制存储过程 无 USE mastergoIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[sp_copyProce]') AND Objectproperty(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[sp_copyProce]goCREATE PROC Sp_copyproce @s_dbname SY

SQL语句复制存储过程

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

人气教程排行