1 背景

一般情况下，在smartbi新建数据源>添加数据库(数据库管理>将表添加到smartbi)后，会同步字段注释到smartbi。

可是由于SQL Server各个版本查询字段注释的方法差异很大，因此产品一直没有支持SQL Server的同步注释功能。

我们可以通过计划任务实现对某个版本SQL Server的字段注释进行同步。

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