SQL server查询数据库中的表和表中的字段,mybatis中的写法

查询数据库中的表

1,查询数据库中的表名

select table_name from information_schema.tables where TABLE_CATALOG='数据库名' 

2,查询数据库中的表及注释

SELECT DISTINCT
            CAST(d.name as varchar) as  name,
            CAST(case when f.value!='' then f.value else '' end as varchar) as value
        FROM syscolumns a
        LEFT JOIN systypes b ON a.xusertype= b.xusertype
        INNER JOIN sysobjects d ON a.id= d.id
                AND d.xtype= 'U'
                AND d.name<> 'dtproperties'
        LEFT JOIN syscomments e ON a.cdefault= e.id
        LEFT JOIN sys.extended_properties f ON d.id= f.major_id
                AND f.minor_id= 0

3,第二个方法写在mybatis会有错误,写在mybatis

关键点:<![CDATA[INNER JOIN sysobjects d ON a.id= d.id
                AND d.xtype= 'U'
                AND d.name<> 'dtproperties' ]]>

 <>在myabtis中无法正确识别

SELECT DISTINCT
            CAST(d.name as varchar) as  name,
            CAST(case when f.value!='' then f.value else '' end as varchar) as value
        FROM syscolumns a
        LEFT JOIN systypes b ON a.xusertype= b.xusertype
        <![CDATA[INNER JOIN sysobjects d ON a.id= d.id
                AND d.xtype= 'U'
                AND d.name<> 'dtproperties' ]]>

        LEFT JOIN syscomments e ON a.cdefault= e.id
        LEFT JOIN sys.extended_properties f ON d.id= f.major_id
                AND f.minor_id= 0

查询表中的字段

1，查询表的字段及注释

SELECT
            CAST(a.name as varchar) as  name,
            CAST(isnull(g.[value],'') as varchar) as  value,
            CAST(b.name as varchar) as  type
        FROM  syscolumns a
        left join  systypes b  on  a.xusertype=b.xusertype
        inner join  sysobjects d  on  a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
        left join  sys.extended_properties  g  on  a.id=G.major_id and a.colid=g.minor_id
        where  d.name=表名
        order by   a.id,a.colorder

2，在mybatis中的写法

SELECT
            CAST(a.name as varchar) as  name,
            CAST(isnull(g.[value],'') as varchar) as  value,
            CAST(b.name as varchar) as  type
        FROM  syscolumns a
                  left join  systypes b  on  a.xusertype=b.xusertype
        <![CDATA[ inner join  sysobjects d  on  a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' ]]>
        left join  sys.extended_properties  g  on  a.id=G.major_id and a.colid=g.minor_id
        where  d.name=#{table}
        order by   a.id,a.colorder