select*from(select cast(so.name as nvarchar(500))as tableName,'mssql'asengine,cast(sep.valueas nvarchar(500))as tableComment, getDate()as createTime
from sysobjects so
leftJOIN sys.extended_properties sep on sep.major_id=so.id and sep.minor_id=0where(xtype='U'or xtype='v')) t where t.tableName=#{tableName}
Oracle
select dt.table_name tableName,dtc.comments tableComment,dt.last_analyzed createTime from user_tables dt,user_tab_comments dtc where dt.table_name=dtc.table_name and dt.table_name = UPPER(#{tableName})
2. 查询数据表里的所有字段
MySQL
select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columnswhere table_name =#{tableName} and table_schema = (select database()) order by ordinal_position
SQLServer
SELECT
cast(
b.NAME AS NVARCHAR(500))AS columnName,
cast(
sys.types.NAME AS NVARCHAR(500))AS dataType,
cast(
c.VALUEAS NVARCHAR(500))AS columnComment,(SELECTCASEcount(1)WHEN1then'PRI'ELSE''ENDFROM
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.tablesUNIONallselect
name,
object_id
from
sys.views
) a
INNERJOIN sys.COLUMNS b ON
b.object_id = a.object_id
LEFTJOIN sys.typesON
b.user_type_id = sys.types.user_type_id
LEFTJOIN 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'
Oracle
selecttemp.column_name columnname,temp.data_type dataType,temp.comments columnComment,casetemp.constraint_type when'P'then'PRI'when'C'then'UNI'else''end"COLUMNKEY",''"EXTRA"from(select col.column_id,
col.column_name,
col.data_type,
colc.comments,
uc.constraint_type,-- 去重
row_number()over(partitionby col.column_name orderby uc.constraint_type desc)as row_flg
from user_tab_columns col
leftjoin user_col_comments colc
on colc.table_name = col.table_name
and colc.column_name = col.column_name
leftjoin user_cons_columns ucc
on ucc.table_name = col.table_name
and ucc.column_name = col.column_name
leftjoin user_constraints uc
on uc.constraint_name = ucc.constraint_name
where col.table_name = upper(#{tableName}))tempwheretemp.row_flg =1orderbytemp.column_id
3. 所有表及其字段一起查询
MySQL
SELECT
t.table_name,
GROUP_CONCAT(c.column_name,'')AScolumnsFROM information_schema.tables t
LEFTJOIN information_schema.columns c on c.table_name = t.table_name
WHERE t.table_schema =(SELECTdatabase())GROUPBY t.table_name
SqlServer
SELECT
SUBSTRING(t.table_name, LENGTH('${prefix}_')+1)AS tableName,
GROUP_CONCAT((CASE data_type WHEN'datetime'THEN CONCAT('CONVERT(varchar(100),', column_name,',20) AS ', column_name)ELSE column_name END),'')AScolumnsFROM information_schema.tables t
LEFTJOIN information_schema.columns c on c.table_name = t.table_name
WHERE t.table_schema =(SELECTdatabase())AND t.table_name LIKE'${prefix}|_%'escape'|'GROUPBY t.table_name