向链接服务器上同步存储过程、函数、视图

by ·一条评论

Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

--EXEC SyncSpToLinkServer @LinkServer='remoteserver1', @Name ='perfmonFix'
--向链接服务器tempdb上同步存储过程、函数、视图
--关键点：在链接服务器上执行sql：EXEC('exec tempdb.dbo.sp_executesql @statement =N''select 1''') AT remoteserver1
CREATE PROCEDURE SyncSpToLinkServer
@LinkServer VARCHAR(255),
@name VARCHAR(255) --存储过程、函数、视图名称
AS
BEGIN
 DECLARE @sql VARCHAR(max)
 SET @sql = '
 DECLARE @IsDrop BIT,@IsCreate BIT,@type VARCHAR(100),@tmp VARCHAR(1000)
 SET @IsDrop = 0 --默认不删除链接服务器上的存储过程
 SET @IsCreate = 1 --默认在链接服务器上创建存储过程

 SELECT
 @IsCreate = CASE WHEN  rp.modify_date <= lp.modify_date THEN 1 ELSE 0 END,--链接服务器存储过程是否不是最新
 @IsDrop = CASE WHEN  rp.modify_date <= lp.modify_date THEN 1 ELSE 0 END,
 @type = CASE(rp.type)
  WHEN ''V'' THEN ''VIEW''
  WHEN ''P'' THEN ''PROCEDURE''
  WHEN ''FN'' THEN ''FUNCTION''
  WHEN ''IF'' THEN ''FUNCTION''
  WHEN ''TF'' THEN ''FUNCTION'' END
 FROM ' + QUOTENAME(@LinkServer) +'.tempdb.sys.objects rp
 JOIN sys.objects  lp ON rp.name = lp.name
 WHERE rp.name = ' + QUOTENAME(@name,'''') +'
 
 SELECT CASE(@IsDrop) WHEN 1 THEN ''drop'' ELSE ''nodrop'' END,
 CASE(@IsCreate) WHEN 1 THEN ''create'' ELSE ''nochanged'' END,@type

 IF @IsDrop = 1
 BEGIN
  
  --SET @tmp = ''exec tempdb.dbo.sp_executesql @statement =N'''' DROP ''  + @type +   ' + '  ' + QUOTENAME(@name,'''') +'
  SET @tmp = ''exec tempdb.dbo.sp_executesql @statement =N''  + ''''''  DROP ''  + @type +   ' + '''  '   + @name +'''  + ''''''''
  PRINT @TMP
  EXEC(@tmp) AT  ' + QUOTENAME(@LinkServer) +'
 END
 IF @IsCreate = 1
 BEGIN
  DECLARE @CreateSql VARCHAR(max), @ExecuteSQL VARCHAR(MAX)
  SELECT  @CreateSql =  REPLACE(definition,'''''''','''''''''''')--处理引号
  from sys.sql_modules where object_id=object_id(' + QUOTENAME(@name,'''') +' )
  IF @CreateSql IS NOT NULL
  BEGIN
   SET @ExecuteSQL = ''EXEC tempdb.dbo.sp_executesql @statement =N'''''' + @CreateSql +''''''''
   EXEC(@ExecuteSQL)  AT ' + QUOTENAME(@LinkServer) +'
  END
 END '

 --PRINT @sql
 EXEC (@SQL)
END