各大數(shù)據(jù)庫的表和表字段信息

1.Mysql

獲取該數(shù)據(jù)庫的表（表名，行數(shù)，表注釋）

SELECT TABLE_NAME as table_name,  
 TABLE_ROWS as table_rows,
 TABLE_COMMENT as table_comment
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名'

獲取該表的字段信息（字段名，字段類型，字段注釋）

SELECT COLUMN_NAME as col, 
 COLUMN_TYPE as type,
 COLUMN_COMMENT as fieldDesc 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = '表名'

2.Orancle

獲取該數(shù)據(jù)庫的表（表名，行數(shù)，表注釋）

SELECT a.table_name AS "table_name", a.num_rows AS "table_rows",b.comments AS "table_comment"
                FROM user_tables a
                LEFT JOIN USER_TAB_COMMENTS b ON a.TABLE_NAME = b.TABLE_NAME 
                WHERE a.TABLESPACE_NAME!='SYSAUX'  
                and a.TABLESPACE_NAME!='EXAMPLE' 
                and  a.table_name not like '%$%'

獲取該表的字段信息（字段名，字段類型，字段注釋）

SELECT a.COLUMN_NAME  AS "col",a.COMMENTS  AS "fieldDesc",b.DATA_TYPE  AS "type" 
FROM user_col_comments a
LEFT JOIN   all_tab_columns b  ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_name = b.TABLE_name
WHERE a.TABLE_name = '表名'

3.sql Server

獲取該數(shù)據(jù)庫的表（表名，行數(shù)，表注釋）

SELECT t.name AS table_name,SUM(p.rows) AS table_rows,c.value AS table_comment
 FROM sys.tables t 
 INNER JOIN sys.partitions p ON t.object_id = p.object_id 
 left join sys.extended_properties c on c.major_id = t.object_id 
 WHERE t.is_ms_shipped = 0 AND p.index_id IN (0,1) 
       GROUP BY t.name,c.value 
       ORDER BY table_rows DESC 

獲取該表的字段信息（字段名，字段類型，字段注釋）

select  b.name as col, c.value  as  column_description ,d.data_type as type
from sys.tables a
inner join sys.columns b on b.object_id = a.object_id
left join sys.extended_properties c on c.major_id = b.object_id and c.minor_id = b.column_id
left join INFORMATION_SCHEMA.COLUMNS d on d.table_name = a.name and d.column_name = b.name
 where a.name = '表名'

4.PostgreSql

獲取該數(shù)據(jù)庫的表（表名，行數(shù)，表注釋）

select f.table_name ,a.table_comment ,e.n_live_tup as table_rows 
from information_schema.tables f 
left join 
     (SELECT c.relname AS table_name,  d.description AS table_comment 
      FROM pg_class AS c
      LEFT JOIN pg_description AS d ON (c.oid = d.objoid AND d.objsubid = 0)
      ) a on a.table_name = f.table_name
       left JOIN pg_stat_user_tables e on f.table_name = e.relname
where f.table_schema = 'public'

獲取該表的字段信息（字段名，字段類型，字段注釋）

SELECT a.attname as col,
format_type(a.atttypid,a.atttypmod) as type,
 col_description(a.attrelid,a.attnum) as fieldDesc   
FROM pg_class as c,pg_attribute as a 
 where 
a.attrelid = c.oid and 
a.attnum>0 and 
c.relname ='表名'

文章來源地址http://www.zghlxwxcb.cn/news/detail-610023.html

到了這里，關于分享一些常用的數(shù)據(jù)庫結構表和字段語句（BI系統(tǒng)數(shù)據(jù)源部分可能會用到）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！