<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.bj1890.avenue.codegen.mapper.GeneratorMssqlMapper">

	<select id="queryTable" resultType="map">
		SELECT *
		FROM (
				 SELECT CAST
							(so.NAME AS nvarchar(500))   AS tableName,
						CAST(sep.VALUE AS nvarchar(500)) AS tableComment
				 FROM sysobjects so
						  LEFT JOIN sys.extended_properties sep ON sep.major_id = so.ID
					 AND sep.minor_id = 0
				 WHERE (xtype = 'U' OR xtype = 'v')
			 ) T
		<where>
			<if test="tableName != null and tableName.trim() != ''">
				and T.tableName = #{tableName}
			</if>
		</where>
	</select>

	<sql id="queryColumn">
		SELECT
			'mssql' AS dbType,
			CAST ( b.NAME AS NVARCHAR ( 500 ) ) AS columnName,
			CAST ( sys.types.NAME AS NVARCHAR ( 500 ) ) AS dataType,
			CAST ( sys.types.NAME AS NVARCHAR ( 500 ) ) AS columnType,
			CAST ( c.VALUE AS NVARCHAR ( 500 ) ) AS comments,
			(
			SELECT
			CASE
				COUNT
					( 1 )
					WHEN 1 THEN
					'PRI' ELSE ''
				END
				FROM
					syscolumns,
					sysobjects,
					sysindexes,
					sysindexkeys,
					systypes
				WHERE
					syscolumns.xusertype = systypes.xusertype
					AND syscolumns.id = object_id( A.NAME )
					AND sysobjects.xtype = 'PK'
					AND sysobjects.parent_obj = syscolumns.id
					AND sysindexes.id = syscolumns.id
					AND sysobjects.NAME = sysindexes.NAME
					AND sysindexkeys.id = syscolumns.id
					AND sysindexkeys.indid = sysindexes.indid
					AND syscolumns.colid = sysindexkeys.colid
					AND syscolumns.NAME = B.NAME
				) AS columnKey,
				'' AS extra
			FROM
				(
				SELECT
					name,
					object_id
				FROM
					sys.tables UNION ALL
				SELECT
					name,
					object_id
				FROM
					sys.views
				) a
				INNER JOIN sys.COLUMNS b ON b.object_id = a.object_id
				LEFT JOIN sys.types ON b.user_type_id = sys.types.user_type_id
				LEFT JOIN sys.extended_properties c ON c.major_id = b.object_id
				AND c.minor_id = b.column_id
			WHERE a.NAME = #{tableName} and  sys.types.NAME != 'sysname'
	</sql>

	<select id="selectTableColumn" resultType="com.bj1890.avenue.codegen.entity.ColumnEntity">
		<include refid="queryColumn"/>
	</select>

	<select id="selectMapTableColumn" resultType="map">
		<include refid="queryColumn"/>
	</select>

</mapper>
