如何获取SQLSERVER 2012 字段 的定义信息进行拼接及处理

1.背景：

        最近接手一个项目，需要把数据从一个SQLSERVER库迁移到到另一个SQLSERVER库，两个库是不同的人设计的，源库是一个历史库，已经不再使用，目标库是当前使用的库，现在需求是要从目标库抽取数据进行上报上级机构，但上传的数据，包含历史库的数据，因为目标库是才开始使用半年的库，半年以前的库全部在历史库中，因此，需要把历史库中的数据全部迁移到当前生产库中来，由于数据分属于两拨人设计，因此，表结构，数据字典都不一样，需要进行数据转换处理。

2.解决思路：

        在目标库建立链接服务器链接到源库，通过游标方式对每行进行处理，然后插入到目标库当前表中去，如下伪语句例子 ：

declare xx int

declare yy numeric(14,2)

declare cursor_bzyy cursor for

   select * from ljserver.dbo.zz;

open cursor cursor_bzyy;

FETCH NEXT FROM BASY_Cursor INTO @xx，@yy

WHILE @@FETCH_STATUS = 0  
   begin
       select  @maxid=isnull(max(id),0) from U_PageA;
      set @xx=@xx+110;

      set @yy=@yy+886;
       insert into U_PageA /*_test*/ (id,@xx,@yy);

      FETCH NEXT FROM BASY_Cursor INTO @xx，@yy

end;

CLOSE BASY_Cursor;  
DEALLOCATE BASY_Cursor;  
go

3.遇到问题：

        在实际处理过程中，发现这个字段特别多，要去写变量定义，都很难，如果只有几十个字段，还好，结果这个表有800多个，一说有800个，群友感觉不可思议：

不管开发是否混得下去，如果我搞不定，我就得跑路呀，没有办法，

即使要修改原字段得类型，查起来，都很难，SQLSERVER自己得工具SSMS，感觉还不是很友好，不提供查找功能：

明明有这个字段，结果找不到，

最后只有通过sp_help table_name ，拷贝到EXCEL，或者使用ULTRAEDIT方式去查

但这样也比较慢，

4.解决办法：

        通过数据字典查询字段定义

select table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,a.IS_NULLABLE,a.COLUMN_DEFAULT
   from INFORMATION_SCHEMA.COLUMNS  a
   where table_catalog='bak'
    and table_name='V_BA_FIRSTPAGE'
    and a.column_name='f_rycs';

通过数据字典产生T-SQL 的字段变量定义信息：

select 
     case  data_type  
        when 'varchar' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' varchar(' + cast(a.CHARACTER_MAXIMUM_LENGTH as varchar) + ')'
        when 'int' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' int' 
        when 'date' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' date'
        when 'datetime' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' datetime'
        when 'numeric' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' numeric(' + cast(a.NUMERIC_PRECISION  as varchar) + ',' + cast(a.NUMERIC_SCALE as varchar) + ')'
        when 'decimal' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' decimal(' + cast(a.NUMERIC_PRECISION as varchar) + ',' +  cast (a.NUMERIC_SCALE as varchar) + ')'
        when 'money' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' money '
        when 'float' then   'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' float '
        else  'declare @' + cast(column_name  as varchar) + ' ' + space(20-len(column_name)) + cast(data_type as varchar)
        end 
   from INFORMATION_SCHEMA.COLUMNS  a
   where table_catalog='bak'
    and table_name='V_BA_FIRSTPAGE'
    order by ORDINAL_POSITION;

结果如下：

一共814个字段

而且，所有字段，在定义时，统一了空格长度，方便阅读，另外，对于INT,MONEY,FLOAT，去掉了长度定义，否则，会报错。

通过数据字典产生字段拼接串，用于插入时，要把所有字段列拼接到一起，好像还有长度限制

比如这里：FETCH NEXT FROM BASY_Cursor INTO @xx

如果直接这样执行：

发现字段没有打印完，丢字段了

变更字段定义得长度，也不行。好像没有招了。

后来在晚上找到一个办法，使用XML，可以解决：

declare @strcol varchar(max)='';
    declare @strsingle varchar(50)='';
    declare column_Cursor CURSOR FOR  
     select column_name
     from INFORMATION_SCHEMA.COLUMNS  a
     where table_catalog='bak'
      and table_name='V_BA_FIRSTPAGE'
      --and ORDINAL_POSITION>=750
      order by ORDINAL_POSITION;

      OPEN column_Cursor;  
      FETCH NEXT FROM column_Cursor INTO @strsingle;
      WHILE @@FETCH_STATUS = 0  
        begin
           set @strcol=@strcol+ ',@' +  @strsingle;
         FETCH NEXT FROM column_Cursor INTO @strsingle;
        end;
      --print @strcol
      select @strcol for xml path ('')
      CLOSE column_Cursor;  

      DEALLOCATE column_Cursor;  
go

双击最后得XML输出，就可以看到所有得了

如果只是想要字段，把@去掉即可

是不是很方便，根据自己得需要变换即可。

5.最后的完整方案：

        这个部分，涉及到具体业务，只有一部分，所以，不太清楚，见谅：

    

最后，介绍一下我自己，10年以上数据库从业经验，有10G OCP,10G OCM,11G OCM ,12C OCM，19C OCM，MYSQL5.7  OCP, PGCE ，ogca等等，为了方便交流，我建立了一个微信群：水煮数据库，主要交流日常工作中用到的数据库问题，包含但不限于：ORACLE,PG，MYSQL，SQLSERVER，OB,TIDB，达梦，TDSQL，OPENGAUSS，人大金仓，GBASE等等，有兴趣的可以加我微信：zq24803366, 我可以拉你入群。