各大数据库的表和表字段信息
1.Mysql
获取该数据库的表(表名,行数,表注释)
SELECT TABLE_NAME as table_name,
TABLE_ROWS as table_rows,
TABLE_COMMENT as table_comment
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '数据库名'
获取该表的字段信息(字段名,字段类型,字段注释)
SELECT COLUMN_NAME as col,
COLUMN_TYPE as type,
COLUMN_COMMENT as fieldDesc
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '表名'
2.Orancle
获取该数据库的表(表名,行数,表注释)
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
获取该数据库的表(表名,行数,表注释)
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
获取该数据库的表(表名,行数,表注释)
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 ='表名'
文章来源地址https://www.toymoban.com/news/detail-610023.html
文章来源:https://www.toymoban.com/news/detail-610023.html
到了这里,关于分享一些常用的数据库结构表和字段语句(BI系统数据源部分可能会用到)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!