从visio数据库脚本生成添加 MS_Description 的sql脚本

最新推荐文章于 2021-02-11 06:10:07 发布

weixin_30747253

最新推荐文章于 2021-02-11 06:10:07 发布

阅读量89

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/MikeYao/archive/2011/06/23/2087827.html

版权

公司每次在数据库新表发布后，都要求立即填上数据字典。每次我发布八九张表，就有几十上百个字段需要填写说明，工作的乏味可想而知。其实，我们在用visio设计数据库时，每个字段都有中文说明，为何不利用已有的说明简化工作呢？于是，网上一搜，在百度博客发现唯一一例与我遇到相同问题的人，TA已经提供了解决方案，稍稍修改即可。不过我很好奇，这样的文章为什么不是出现在博客园这样技术园子里？呵，闲话姑且放一放，先上我修改后的版本，再附上原文。

< textarea name ='text1' cols =160 rows =28 ></ textarea >
< br >< br >
< center >
     < input type ='button' onclick ='x1()' value =" 第一步 " >
     < input type ='button' onclick ='x2()' value =" 第二步 " >
</ center >
< script >
function x1(){
  text1.value = text1.value.replace( / [ / ][ * ] / g,"")
  text1.value = text1.value.replace( / [*][ / ] / g,",")
  text1.value = text1.value.replace( / / g,"")
  text1.value = text1.value.replace( / ["] / g, "" )
  text1.value = text1.value.replace( / [:] / g, " , " )
  text1.value = text1.value.replace( / \t / g, "" )
  text1.value = text1.value.replace( / \r\n\r\n / , "" );
   if (text1.value.substring( 0 , 2 ) == " \r\n " ){
    text1.value = text1.value.substring( 2 );
  }
   if (text1.value.substring( 0 , 14 ) == " Createnewtable " ){
    text1.value = text1.value.substring(text1.value.indexOf( " \r\n " ) + 2 );
  }

   var k
   var r = "" ;
  k = text1.value.split( " \r\n " );
   var tabName = k[ 0 ].split( " , " )[ 0 ];
   for ( var i = 1 ;i < k.length;i ++ ){
      var x = k[i];
      var h = x.split( " , " );
      if (h[ 0 ] != "" ) {
     r += h[ 1 ]     + " , " +
         tabName + " , " +
         h[ 0 ]     + " \r\n " ;
    }
  }
  text1.value = r.substring( 0 ,r.length - 2 );
}
function x2(){
   var k
   var r = "" ;
  k = text1.value.split( " \r\n " );
   for ( var i = 0 ;i < k.length;i ++ ){
      var h = k[i].split( " , " );
     r += " exec sp_addextendedproperty N'MS_Description', N' " +
            h[ 0 ] + " ', N'SCHEMA', N'dbo', N'table', N' " +
            h[ 1 ]   + " ', N'column', N' " +
            h[ 2 ] + " ' " + " \r\n "
  }
  text1.value = r;
}
</ script >

可下载文件： /Files/BenjaminYao/从visio数据库脚本生成添加MS_Description的sql脚本.htm

改进版： /Files/BenjaminYao/从visio数据库脚本生成添加MS_Description的sql脚本.v2.0.7z

可测试文本：

/* Create new table "ScheduleDetail_extend".                                                  */
/* "ScheduleDetail_extend" : 排班明细扩展表                                                 */
/*    "ScheduleDetailExtendID" : 排班明细扩展表ID                                   */
/*    "ScheduleDetailID" : 排班明细ID                                            */
/*    "DimScheduleDetailExtendCategoryId" : 排班明细扩展类别Id                            */

原文如下，转载自

http://hi.baidu.com/mkjxknioitvere/blog/item/227d5ad270d0fb3d960a165a.html

根据visio导出的建表脚本生成用于添加备注的t-sql语句
比如如下的是visio导出的建表脚本之一:
--------------------------------------------------------------------------------------------------------------
/* 创建新表 "TransAgr"。                                                                           */
/* "TransAgr" : Table of 运输合同                                                                 */
/*     "TransArgCode" : 运输合同编号                                                                   */
/* "FrePrice" : 运价                                                                           */
/* "mileage" : 运距                                                                            */
/* "MatCode" : 承运物资                                                                          */
/* "TraCode" : 运输部门                                                                          */
/* "UnitCode" : 物资所属单位                                                                       */
/* "CFID" : 运费结算方式                                                                           */
create table "TransAgr" (
"TransArgCode" nchar(20) not null,
"FrePrice" decimal(10,0) not null,
"mileage" decimal(5,1) null,
"MatCode" char(10) not null,
"TraCode" char(10) null,
"UnitCode" char(10) null,
"CFID" tinyint not null)

go
--------------------------------------------------------------------------------------------------------------
把/**/括住的行复制到文本框:

    /* 创建新表 "TransAgr"。                                                                           */
    /* "TransAgr" : Table of 运输合同                                                                 */
    /* "TransArgCode" : 运输合同编号                                                                   */
    /* "FrePrice" : 运价                                                                           */
    /* "mileage" : 运距                                                                            */
    /* "MatCode" : 承运物资                                                                          */
    /* "TraCode" : 运输部门                                                                          */
    /* "UnitCode" : 物资所属单位                                                                       */
    /* "CFID" : 运费结算方式                                                                           */

依次按两个按钮，生成如下脚本:

exec sp_addextendedproperty N ' MS_Description ' , N ' 运输合同编号 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' TransArgCode '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 运价 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' FrePrice '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 运距 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' mileage '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 承运物资 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' MatCode '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 运输部门 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' TraCode '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 物资所属单位 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' UnitCode '
     exec sp_addextendedproperty N ' MS_Description ' , N ' 运费结算方式 ' , N ' user ' , N ' dbo ' , N ' table ' , N ' TransAgr ' , N ' column ' , N ' CFID '

=========================================================================================================

---------------------------------------------------------------

< textarea name ='text1' cols =160 rows =28 ></ textarea >
< input type ='button' onclick ='x1()' value =" x1 " >
< input type ='button' onclick ='x2()' value =" x2 " >
< script >
function x1(){
  text1.value = text1.value.replace( / [ / ][ * ] / g,"")
  text1.value = text1.value.replace( / [*][ / ] / g,",")
  text1.value = text1.value.replace( / / g,"")
  text1.value = text1.value.replace( / ["] / g, "" )
  text1.value = text1.value.replace( / [:] / g, " , " )
  text1.value = text1.value.replace( / \t / g, "" )
  text1.value = text1.value.replace( / \r\n\r\n / , "" );
   if (text1.value.substring( 0 , 2 ) == " \r\n " ){
    text1.value = text1.value.substring( 2 );
  }
   if (text1.value.substring( 0 , 4 ) == " 创建新表 " ){
    text1.value = text1.value.substring(text1.value.indexOf( " \r\n " ) + 2 );
  }

   var k
   var r = "" ;
  k = text1.value.split( " \r\n " );
   var tabName = k[ 0 ].split( " , " )[ 0 ];
   for ( var i = 1 ;i < k.length;i ++ ){
      var x = k[i];
      var h = x.split( " , " );
     r += h[ 1 ]     + " , " +
         tabName + " , " +
         h[ 0 ]     + " \r\n " ;
  }
  text1.value = r.substring( 0 ,r.length - 2 );
}
function x2(){
   var k
   var r = "" ;
  k = text1.value.split( " \r\n " );
   for ( var i = 0 ;i < k.length;i ++ ){
      var h = k[i].split( " , " );
     r += " exec sp_addextendedproperty N'MS_Description', N' " +
            h[ 0 ] + " ', N'user', N'dbo', N'table', N' " +
            h[ 1 ]   + " ', N'column', N' " +
            h[ 2 ] + " ' " + " \r\n "
  }
  text1.value = r;
}
</ script >

转载于:https://www.cnblogs.com/MikeYao/archive/2011/06/23/2087827.html