ER図の論理名をコメントに設定する

A5:SQLでER図の論理名をコメントに設定するスクリプトを書いてみました。スクリプトをツールフォルダに保存して，ツールメニューから実行するとSQLウィンドウにSQL文をはき出します。

 // ER図の論理名をコメントに設定(&C)
 
 function isSQLServer(dbName){
     var regini = new RegIni("Software\\mmatsubara\\A5M2");
     var s = regini.read("DataSources",dbName,"");
     return (s.match(/Provider=SQLOLEDB.*/) || s.match(/Provider=SQLNCLI.*/));
 }
 
 function delQuate(s){
     var r = s.replace(/^"/i,"");
     r = r.replace(/"$/,"");
     r = r.replace(/""/g,'"');
     return r;
 }
 
 function tableExists(cnn, sTable){
     var tables = cnn.getTableList();
     var exp = new RegExp(sTable,"i");
     return tables.toString().match(exp);
     //return tables.indexOf(sTable) >= 0;
 }
 
 function fieldExists(cnn, sTable, sField){
     if(tableExists(cnn, sTable)){
         var fields = cnn.getTableColumnList(sTable);
         var fieldNames = new Array(fields.length);
         for(var i=0; i<fields.length; i++){
             fieldNames[i] = fields[i].columnName;
         }
         writeln(fieldNames.toString());
         var exp = new RegExp(sField,"i");
         return fieldNames.toString().match(exp);
     }else{
         return false;
     }
 }
 
 function setDescriptionMSSQL(cnn, sTable, sTDescription, sField, sFDescription){
     var sCmdField, rs, sCmd, sDescription;
     if(isNull(sField)){
         sCmdField = "NULL, NULL";
         sDescription = sTDescription;
     }else{
         sCmdField = "'COLUMN','"+sField+"'";
         sDescription = sFDescription;
     }
     rs = cnn.executeQuery("select value from fn_listextendedproperty(
         'MS_Description', 'SCHEMA', 'dbo', 'TABLE', '"+sTable+"', "+sCmdField+");");
     sCmd = "N'MS_Description', N'"+sDescription+"', N'SCHEMA', N'dbo', N'TABLE', N'"+
         sTable+"', "+sCmdField+";\n";
     if(rs.eof()){
         return "EXECUTE sp_addextendedproperty "+sCmd;
     }else{
         return "EXECUTE sp_updateextendedproperty "+sCmd;
     }
 }
 
 function setDescriptionOra(cnn, sTable, sTDescription, sField, sFDescription){
     if(isNull(sField)){
         return "comment on table "+sTable+" is '"+sTDescription+"';\n";
     }else{
         return "comment on column "+sTable+"."+sField+" is '"+sFDescription+"';\n";
     }
 }
 
 function setDescription(cnn, sSQL, sTable, sTDescription, sField, sFDescription){
     var isComment;
     if(isNull(sField)){
         if(sTDescription.length==0){
             return sSQL;
         }
         isComment = !tableExists(cnn, sTable);
     }else{
         if(sFDescription.length==0){
             return sSQL;
         }
         isComment = !fieldExists(cnn, sTable, sField);
     }
     if(isComment){
         sSQL = sSQL + '-- ';
     }
     var dbName = application.dbTree.getSelectedDatabaseName();
     if(isSQLServer(dbName)){
         return sSQL + setDescriptionMSSQL(cnn, sTable, sTDescription, sField, sFDescription);
     }else{
         return sSQL + setDescriptionOra(cnn, sTable, sTDescription, sField, sFDescription);
     }
 }
 
 function SetLogicalName(){
     var cnn = application.dbTree.getSelectedDatabaseConnection();
     var dbName = application.dbTree.getSelectedDatabaseName();
     var form = application.getActiveWindow();
     var sSQL = '';
     if(isUndefined(dbName)){
         alert('更新対象のデータベースを選択してください');
         return;
     }
     if(form.formType != 'A5EREditor'){
         alert('ERエディタ上で実行してください');
         return;
     }
     var s, A, inEntity=false, sTableP, sTableL, sField, aField;
     var sTableCmd, sFieldCmd;
     for(var I=0; I<form.source.Count; I++){
         s = form.source[I];
         if(s.match(/^\[.*\]$/)){
             inEntity = s.match(/\[Entity\]/);
         }
         if(inEntity){
             if(s.match(/PName=.*/)){
                 A = s.split(/=/);
                 sTableP = A[1];
             }
             if(s.match(/LName=.*/)){
                 A = s.split(/=/);
                 sTableL = A[1];
                 sSQL = sSQL + "-- Table " + sTableP + "\n";
                 sSQL = setDescription(cnn, sSQL, sTableP, sTableL, null, null);
             }
             if(s.match(/Field=.*/)){
                 A = s.split(/=/);
                 sField = A[1];
                 aField = sField.split(/,/);
                 for(var J=0; J<aField.length; J++){
                     aField[J] = delQuate(aField[J]);
                 }
                 sSQL = setDescription(cnn, sSQL, sTableP, sTableL, aField[1], aField[0]);
             }
         }
     }//for
     var frmSQL = application.newSqlEditor(sSQL);
     //frmSQL.runSql();
     //application.dbTree.reload();
 }
 
 
 SetLogicalName();