<?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.hccake.ballcat.codegen.database.sqlserver.SqlServerTableInfoMapper">
    <select id="selectByPage" resultType="com.hccake.ballcat.codegen.model.bo.TableInfo">
		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
		<if test="tableName != null and tableName.trim() != ''">
			WHERE t.tableName LIKE concat('%', #{tableName}, '%')
		</if>
    </select>


	<select id="queryTableInfo" resultType="com.hccake.ballcat.codegen.model.bo.TableInfo">
		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
			  t.tableName = #{tableName}
	</select>


	<select id="listColumnInfo" resultType="com.hccake.ballcat.codegen.model.bo.ColumnInfo">
		SELECT
			cast(b.NAME AS NVARCHAR(500)) AS columnName,
			cast(sys.types.NAME AS NVARCHAR(500)) AS dataType,
			cast(c.VALUE AS NVARCHAR(500)) AS columnComment,
			(
				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'
	</select>


</mapper>
