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

      公司每次在数据库新表发布后，都要求立即填上数据字典。每次我发布八九张表，就有几十上百个字段需要填写说明，工作的乏味可想而知。其实，我们在用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,"")
}
function x2(){
  var k
  var r="";
  k = text1.value.split("\r\n");
  for(var i=0;i<k.length;i++){
     var x = k[i]
     var h=k[i].split(",");
     r+= "exec sp_addextendedproperty N'MS_Description', N'" + 
            h[1] + "', N'user', N'dbo', N'table', N'" + 
            h[2] + "', N'column', N'" + 
            h[0] + "'" + "\r\n"
  }
  text1.value=r;
}
</script>

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

<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>