sqlserver表和字段增加或更新注释-蒲公英云

sqlserver表和字段增加或更新注释

针对 SpringBoot JPA + sqlserver 这种生成的表没有注释的情况，通过代码更新注释方法整理如下：

// SpringBoot JPA 可以直接引入 jdbcTemplate Bean
@Autowired
private JdbcTemplate jdbcTemplate;
private void setTableComment(String tableName, String tableComment) { 
        // 1表名称 2字段名称
        String checkTableCommentExistsSql = "SELECT COUNT(*) FROM ::fn_listextendedproperty ('MS_Description','SCHEMA','dbo','TABLE',?,NULL,NULL)";
        // 1注释 2表名称 3字段名称
        String updateTableComment = " execute sp_updateextendedproperty 'MS_Description',?,'SCHEMA','dbo','TABLE',?";
        // 1注释 2表名称 3字段名称
        String createTableComment = " execute sp_addextendedproperty 'MS_Description',?,'SCHEMA','dbo','TABLE',?";
        Integer count = jdbcTemplate.queryForObject(checkTableCommentExistsSql, Integer.class, tableName);
        if (count == null || count.equals(0)) { 
            jdbcTemplate.update(createTableComment, tableComment, tableName);
        } else { 
            jdbcTemplate.update(updateTableComment, tableComment, tableName);
        }
    }
    private void setFildComment(String tableName, String fildName, String fildComment) { 
        // 1表名称 2字段名称
        String checkFildCommentExistsSql = "SELECT COUNT(*) FROM ::fn_listextendedproperty ('MS_Description','SCHEMA','dbo','TABLE',?,'COLUMN',?)";
        // 1注释 2表名称 3字段名称
        String updateFildComment = " execute sp_updateextendedproperty 'MS_Description',?,'SCHEMA','dbo','TABLE',?,'COLUMN',? ";
        // 1注释 2表名称 3字段名称
        String createFildComment = " execute sp_addextendedproperty 'MS_Description',?,'SCHEMA','dbo','TABLE',?,'COLUMN',? ";
        Integer count = jdbcTemplate.queryForObject(checkFildCommentExistsSql, Integer.class, tableName, fildName);
        if (count == null || count.equals(0)) { 
            jdbcTemplate.update(createFildComment, fildComment, tableName, fildName);
        } else { 
            jdbcTemplate.update(updateFildComment, fildComment, tableName, fildName);
        }
    }