syscomments :此表存放着存储过程的语句

 /*从链接服务器上拿到存储过程，在目标服务器上执行，可用于发布与更新*/
DECLARE @text VARCHAR(MAX),@objectName VARCHAR(100),@objectID int
DECLARE p CURSOR FOR
SELECT NAME,id FROM dpserver.brm_lvjian_new.dbo.sysobjects o
WHERE xtype IN('P') 
AND o.crdate >= GETDATE()-3
OPEN p
FETCH NEXT FROM p INTO @objectName,@objectID
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @text = ''
    SELECT @text = @text + convert(VARCHAR(MAX),text) FROM dpserver.brm_lvjian_new.dbo.syscomments s 
    WHERE id = @objectID
    IF ( EXISTS( SELECT 1 FROM sysobjects WHERE NAME = @objectName AND xtype IN('P')  ) )
    BEGIN
        --
        EXEC ('DROP PROCEDURE dbo.' +  @objectName)
    END
    EXEC(@text)
    FETCH NEXT FROM p INTO @objectName,@objectID    
END
CLOSE p
DEALLOCATE p