ruoyi（若依）系统使用SqlServer数据库

近期使用若依前后端分离版开发一个公司内部系统，由于指定使用SqlServer数据库，所以采用若依3.7.0版，自己转换脚本及对应查询。

具体修改如下：

1、admin把mysql引用替换为

<dependency>
   <groupId>com.microsoft.sqlserver</groupId>
   <artifactId>mssql-jdbc</artifactId>
</dependency>

2、数据库配置修改：driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

其他就是连接及用户密码信息

# 配置检测连接是否有效
 validationQuery: select 'x'

分页pagehelper配置
  helperDialect: sqlserver

3、定时任务配置ScheduleConfig类设置sqlserver 启用配置

4、SQL语句函数修改

ifnull 替换为 isnull 
find_in_set find_in_set(#{deptId}, ancestors) 替换为  charindex (',' + CONVERT (VARCHAR, #{deptId}), ',' + ancestors) > 0
concat 替换为 ''+''
sysdate  替换为 getdate
date_format date_format(u.create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d') 替换为 DATEDIFF(day, u.create_time , #{params.endTime}) >= 0 
limit 1    替换为 top(1) （注意位置差异）

5、生成代码包SQL修改

selectDbTableList 修改为

SELECT
   		so.name table_name,
		sep.value table_comment,
		so.create_date create_time,
		so.modify_date update_time
		FROM
		sys.objects AS so
		LEFT JOIN sys.extended_properties AS sep ON so.object_id = sep.major_id
		WHERE
		so.type = 'U'
		AND sep.minor_id = 0
		AND so.name NOT LIKE 'qrtz_%' AND so.name NOT LIKE 'gen_%'
		AND so.name NOT IN (select table_name from gen_table)

selectDbTableListByNames修改为

SELECT
		SO.name table_name,
		SEP.VALUE table_comment,
		SO.create_date create_time,
		SO.modify_date update_time
		FROM
		sys.objects AS SO
		LEFT JOIN sys.extended_properties AS SEP ON SO.object_id = SEP.major_id
		WHERE
		SO.type = 'U'
		AND SEP.minor_id = 0
		and SO.name NOT LIKE 'qrtz_%' and SO.name NOT LIKE 'gen_%'
		and SO.name in
	    <foreach collection="array" item="name" open="(" separator="," close=")">
 			#{name}
        </foreach> 

selectDbTableColumnsByName修改为

SELECT a.name AS column_name,
	   (
		   CASE
			   WHEN a.isnullable = 1 THEN
				   0
			   ELSE
				   1
			   END
		   )  AS is_required,
	   (
		   CASE
			   WHEN (
						SELECT COUNT(*)
						FROM sysobjects
						WHERE (
								name IN (
								SELECT name
								FROM sysindexes
								WHERE (id = a.id)
								  AND (
										indid IN (
										SELECT indid
										FROM sysindexkeys
										WHERE (id = a.id)
										  AND (
												colid IN (
												SELECT colid
												FROM syscolumns
												WHERE (id = a.id)
												  AND (name = a.name)
											)
											)
									)
									)
							)
							)
						  AND (xtype = 'PK')
					) > 0 THEN
				   1
			   ELSE
				   0
			   END
		   )  AS is_pk,
	   a.colorder  AS sort,
	   isnull(g.[value], ' ') AS column_comment,
	   (
		   CASE
			   WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN
				   1
			   ELSE
				   0
			   END
		) AS is_increment,
	   b.name  AS column_type
FROM syscolumns a
		 LEFT JOIN systypes b ON a.xtype = b.xusertype
		 INNER JOIN sysobjects d ON a.id = d.id
	AND d.xtype = 'U'
	AND d.name  <![CDATA[ <> ]]> '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.class
	AND f.minor_id = 0
		 LEFT JOIN sys.objects h ON a.id = h.object_id
		 LEFT JOIN sys.schemas i ON h.schema_id = i.schema_id
WHERE d.name = #{tableName}
ORDER BY a.colorder

参考：sqlserver查看表名及表注释_毒牙-CSDN博客_sqlserver获取表注释

sqlserver 获取schema、表、字段等关键信息_aixin999的博客-CSDN博客