mysql oracle SQL server系统表

--------------------------------------------------------------oracle 

--查询表信息
select t.TABLE_NAME,b.comments from user_tables t
join user_tab_comments b on t.TABLE_NAME=b.table_name ;

--查询表注释

select t.table_name,t.comments from user_tab_comments T WHERE T.table_name='GZK_GJJ_GRXX';

--oracle 查询表中列信息               
select t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.NULLABLE,b.comments from user_tab_columns t
join user_col_comments b on t.COLUMN_NAME=b.column_name and b.table_name='GZK_GJJ_GRXX'
 where t.Table_Name='GZK_GJJ_GRXX';

--- 含主键字段
 select t.column_name,
       t.DATA_TYPE,
       t.DATA_LENGTH,
       t.NULLABLE,
       case
         when a.column_name is not null then
          '是'
         else
          '否'
       end sfwk,-- 是否主键
       b.comments
  from user_tab_columns t
  join user_col_comments b
    on t.column_name = b.column_name
   and b.table_name = 'GZK_GJJ_GRXX'
  left join (select col.column_name
               from user_constraints con
               join user_cons_columns col
                 on con.constraint_name = col.constraint_name and col.table_name = 'GZK_GJJ_GRXX'
              where con.constraint_type = 'P') a
    on t.column_name = a.column_name
 where t.Table_Name = 'GZK_GJJ_GRXX';

-----------------------------------------------------------------mysql

--mysql 查询表信息
SELECT table_name name,TABLE_COMMENT comments FROM INFORMATION_SCHEMA.TABLES
 WHERE table_schema = 'phone' order by table_name asc;

-- 查询表的列信息
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,substr(IS_NULLABLE,1,1),column_comment
FROM INFORMATION_SCHEMA.Columns WHERE 
table_name='t_gz_sjhm' AND table_schema='phone';

-- 加是否是主键列

SELECT b.COLUMN_NAME,b.DATA_TYPE,b.CHARACTER_MAXIMUM_LENGTH,substr(b.IS_NULLABLE,1,1) sfwk,
case when a.COLUMN_NAME is not null then '是' else '否' end sfwzj,b.column_comment
FROM INFORMATION_SCHEMA.Columns   b
left join 
(select t.COLUMN_NAME from information_schema.KEY_COLUMN_USAGE t 
where t.CONSTRAINT_SCHEMA='phone' and t.CONSTRAINT_NAME='PRIMARY' and t.TABLE_NAME='test01') a 
on a.COLUMN_NAME = b.COLUMN_NAME
where b.table_name='test01' AND b.table_schema='phone';

------------------------------------------------SQL server

--查询所有表 
SELECT d.Name ,isnull(g.[value], ' ') AS comments FROM SysObjects  d
left join sys.extended_properties g on d.id=g.major_id AND g.minor_id='0'
Where d.XType='U' and d.name<>'dtproperties' ORDER BY d.Name;

--查询表结构信息
SELECT 
a.colorder xh,a.name COLUMN_NAME,
b.name DATA_TYPE,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as length, (case when a.isnullable=1 then 'Y'else 'N' end) sfwk, isnull(g.[value], ' ') AS comments
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<>'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
where b.name is not null
and d.name='t_user' --如果只查询指定表,加上此条件
order by a.id,a.colorder;