Copy stored procedure

The stored procedure of SQL statement copying does not have USEmastergoIFEXISTS (SELECT * FROMdbo. sysobjectsWHEREidObject_id (N [dbo]. [sp_copyProce]) ANDObjectproperty (id, NIsProcedure) 1) DROPPROCEDURE [dbo]. [sp_copyProce] goCREATEPROCSp_copyproce @ s_dbnameSY

SQL statement replication stored procedures do not have 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 statement copy stored procedure <无>

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 SYSNAME, -- name of the source database to copy the Stored Procedure @ d_dbname SYSNAME -- name of the target database as set nocount on if Db_id (@ s_dbname) is null begin raiserror ('database "% s" does not exist ', @ s_dbname) return end if Db_id (@ d_dbname) is null begin raiserror ('database "% s" does not exist ', @ d_dbname) return end select @ s_dbname =' ['+ Replace (@ s_dbname,'] ', ']') + ']', @ d_dbname = '[' + Replace (@ d_dbname, ']', ']') + ']' -- copy stored procedure information to the temporary table 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 -- copy the stored procedure structure 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 (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 overridego