1 背景
一般情况下，在smartbi新建数据源-添加数据库后，会同步字段注释到smartbi。可是由于MSSQL各个版本查询字段注释的方法差异很大，因此产品一直没有支持MSSQL的同步注释功能。
不过我们可以通过计划任务来实现对某个版本sqlserver的字段注释进行同步。
2 使用方法。（这里以MSSQL 2008R2 为例）
【计划任务】【任务】【新建任务】，填写任务名称，选择任务类型：定制。添加以下代码
importPackage(Packages.smartbi.sdk.service.datasource);
importClass(java.lang.System);
importPackage(Packages.smartbi.freequery.querydata);
importPackage(Packages.smartbi.freequery.repository);
importPackage(Packages.smartbi.freequery.basicdata);
importClass(java.util.List);
importPackage(Packages.smartbi.state);
importPackage(Packages.smartbi.fake);
importPackage(Packages.smartbi.usermanager);
var dataSourceId = "DS.sqlserver2008Copy";//需要修改的数据源ID
var tableName = ["student","student_copy"];//需要更新的表名，格式：["表1","表2","表3"...]；当"var tableName = []" 时执行全局更新。
var datasrcService = new DataSourceService(connector);
var mds = MetaDataServiceImpl.getInstance();
var sql = "SELECT A.name AS table_name,B.name AS column_name,C.value AS column_description FROM sys.tables A INNER JOIN sys.columns B ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id";
if(tableName.length > 0){
var nameStr = "";
//拼接表名字符串，作为sql查询条件。
for (var i = 0; i < tableName.length; i++){
if(i == 0)
nameStr = "'"+tableName[i] + "'";
else
nameStr += ",'" + tableName[i]+"'";
}
sql += " WHERE A.name in (" + nameStr + ")";
}
//执行sql
var gridDataGroup = datasrcService.executeNoCacheable(dataSourceId, sql);
System.out.println("别名个数:"+gridDataGroup.getRowsCount());
var dataSource = mds.getDataSource(dataSourceId);
var tables = dataSource.getTables();
//设置会话
var req = new FakeHttpRequest();
StateModule.getInstance().setRequest(req);
StateModule.getInstance().setSession(req.getSession());
req.getSession().setAttribute("state", new State());
StateModule.getInstance().setCurrentUser(UserManagerModule.getInstance().getUserByName("admin"));
//开始同步字段注释
for (var i = 0; i < gridDataGroup.getRowsCount(); i++) {
var hasUpdateOneField = false;
//获取查询结果【表名、字段名称、注释】
var tableName = gridDataGroup.get(i, 0).getValue();
var fieldName = gridDataGroup.get(i, 1).getValue();
var fieldAlias = gridDataGroup.get(i, 2).getValue();
if(fieldAlias && !"".equals(fieldAlias)){
for(var j = 0 ; j < tables.size() ; j++){
var table = tables.get(j);
if(table.getName().equals(tableName)){
var fields = table.getFields();
for(var k = 0 ; k < fields.size() ; k++){
var field = fields.get(k);
if(field.getName().equals(fieldName) && !fieldAlias.equals(field.getAlias())){
field.setAlias(fieldAlias);
hasUpdateOneField = true;
try{
mds.updateField(field);//更新字段
}catch(e){
//数据库中可能存在同名注释，在同名注释后加_fieldName，
field.setAlias(fieldAlias+"_"+fieldName);
mds.updateField(field); //更新字段
}
break;
}
}
}
if(hasUpdateOneField){
break;
}
}
}
}
修改下图中红色框的配置项，保存，测试运行。即可同步MSSQL 2008R2 的字段注释到smartbi。
3 扩展：MSSQL其他版本的查询字段注释语句
我们可以通过修改这个计划任务中的sql语句来同步不同版本的MSSQL字段注释。相应的sql查询语句如下：（可能需要根据实际情况，调整再放到这个计划任务中）
MSSQL 2000:
select so.name as tableName,sc.name as columnName,sp.value as remarks
from sysobjects so
left outer join syscolumns sc on so.id = sc.id
left outer join sysproperties sp on sc.id = sp.id and sc.colid = sp.smallid
where so.type = 'u' and so.name='$tableName$'
order by so.id, sc.colorder
MSSQL 2005：
SELECT tableName = D.NAME ,columnName=A.NAME, remarks=ISNULL(G.[VALUE], ' ')
FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE= 'U ' AND D.NAME <> 'DTPROPERTIES '
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID
LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id
LEFT JOIN sys.extended_properties F ON D.ID=F.major_id AND F.minor_id=0
where D.NAME='$tableName$'
ORDER BY A.ID,A.COLORDER
