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

分手后的思念是犯贱 2021-09-24 12:24 645阅读 0赞

针对 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);
            }
        }