基于SQL SERVER 数据仓库，ODS与源数据库字段检查，源表调整后，目标表自动调整

基于SQL SERVER 数据仓库，ODS与源数据库字段检查，源表调整后，目标表自动调整

ALTER PROCEDURE [dbo].[cp_ODS源表字段检查调整]
AS
BEGIN
EXEC [sys].[sp_MSforeachtable]
     @command1='
  DROP TABLE IF EXISTS [#TEMP_NAME]
SELECT [value]=TRIM(TRANSLATE([value],''[]'',SPACE(2)))
      ,[index]=ROW_NUMBER()OVER(ORDER BY GETDATE()) 
  INTO [#TEMP_NAME]
  FROM STRING_SPLIT(''?'',''.'')
IF ''?'' LIKE ''[[]云医疗%'' BEGIN
DECLARE @DBLINK NVARCHAR(100)=(SELECT [value] FROM [#TEMP_NAME] WHERE [index]=1)
       ,@DB     NVARCHAR(100)=(SELECT [value] FROM [#TEMP_NAME] WHERE [index]=2)
       ,@SCHEMA NVARCHAR(100)=''dbo''
       ,@TABLE  NVARCHAR(100)=(SELECT [value] FROM [#TEMP_NAME] WHERE [index]=3) 
DECLARE @SROUCE NVARCHAR(500)=CONCAT_WS(''.'',QUOTENAME(@DBLINK),QUOTENAME(@DB),QUOTENAME(@SCHEMA),QUOTENAME(@TABLE))
       ,@SQL    NVARCHAR(MAX)=CONCAT(''SELECT * FROM ['',@DBLINK,''].['',@DB,''].[INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='''''',@TABLE,'''''' '')     
       ,@SFTN   NVARCHAR(500)=CONCAT(QUOTENAME(@DBLINK),''.'',QUOTENAME(@DB),''.[INFORMATION_SCHEMA].[COLUMNS]'')
   SET @SQL=CONCAT(''
SELECT @TGT_NAME='''''',QUOTENAME(@DBLINK),''.['',@DB,''.'',@TABLE,'']''''
  FROM '',@SFTN,'' src 
  LEFT JOIN [INFORMATION_SCHEMA].[COLUMNS] tgt
    ON tgt.[COLUMN_NAME]=src.[COLUMN_NAME]
   AND tgt.[TABLE_NAME]='''''',CONCAT(@DB,''.'',@TABLE),''''''
 WHERE src.[TABLE_NAME]='''''',@TABLE,''''''
   AND tgt.[COLUMN_NAME] IS NULL
  '')
  DECLARE @TGT_TABLE_NAME NVARCHAR(200)
  EXEC SP_EXECUTESQL @SQL,N''@TGT_NAME NVARCHAR(200) OUTPUT'',@TGT_NAME=@TGT_TABLE_NAME OUTPUT
   
  IF @TGT_TABLE_NAME IS NOT NULL BEGIN
     SET @SQL=CONCAT(''
       DROP TABLE IF EXISTS '',@TGT_TABLE_NAME,'';
       EXEC [dbo].[cp_ODS集成构造器] 
            @source = '''''',@SROUCE,'''''';'') 
      PRINT (@SQL)
       EXEC SP_EXECUTESQL @SQL
  END
END
     '
 
END