SQLServer实现两个库的字段长度自动更新

本文主要是介绍SQLServer实现两个库的字段长度自动更新，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

-- 以src

-- 1.src数据插入记录表Meta_Src_CharType
SELECT T.TABLE_CATALOG,T.TABLE_NAME,T.TABLE_SCHEMA,T.TABLE_TYPE,C.COLUMN_NAME,C.DATA_TYPE,C.CHARACTER_MAXIMUM_LENGTH,C.CHARACTER_OCTET_LENGTH
  FROM DB_Src.INFORMATION_SCHEMA.TABLES T
 INNER JOIN DB_Src.INFORMATION_SCHEMA.COLUMNS C ON (T.TABLE_CATALOG = C.TABLE_CATALOG AND T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME)
 WHERE T.TABLE_TYPE = 'BASE TABLE'
   AND C.DATA_TYPE LIKE '%CHAR%'

-- 2.对比ODS层，生成ALTER语句，插入Meta_log_AutoModify表
SELECT A.*
       ,B.TABLE_CATALOG NewTABLE_CATALOG
       ,B.TABLE_SCHEMA NewTABLE_SCHEMA
       ,B.CHARACTER_MAXIMUM_LENGTH NewCHARACTER_MAXIMUM_LENGTH
       ,B.CHARACTER_OCTET_LENGTH NewCHARACTER_OCTET_LENGTH
       ,getdate() ModifyDate
       ,'ALTER TABLE ['+B.TABLE_CATALOG+'].['+B.TABLE_SCHEMA+'].[' +B.TABLE_NAME+ '] ALTER COLUMN [' + B.COLUMN_NAME + '] ' + B.DATA_TYPE + '('+CASE WHEN A.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END +')' EXECSQL
  FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY A.TABLE_NAME,COLUMN_NAME ORDER BY CHARACTER_MAXIMUM_LENGTH DESC ) RN FROM Meta_Src_CharType A) A
INNER JOIN DB_ODS.INFORMATION_SCHEMA.COLUMNS B ON (A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND A.DATA_TYPE = B.DATA_TYPE)
WHERE A.RN = 1
  AND CASE WHEN A.CHARACTER_MAXIMUM_LENGTH=-1 THEN 999999 ELSE A.CHARACTER_MAXIMUM_LENGTH END  >  CASE WHEN B.CHARACTER_MAXIMUM_LENGTH=-1 THEN 999999 ELSE B.CHARACTER_MAXIMUM_LENGTH END
  
  
-- 3.执行步骤2中的EXECSQL

这篇关于SQLServer实现两个库的字段长度自动更新的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！