当前位置：首页 > news >正文

数据库查询表结构和数据量以及占用空间

news 2025/6/28 3:29:40

数据库查询表结构和数据量以及占用空间

数据库查询表结构

mysql

SELECT  COLUMN_NAME 列名,  COLUMN_TYPE 数据类型,  DATA_TYPE 字段类型,  CHARACTER_MAXIMUM_LENGTH 长度,  IS_NULLABLE 是否为空,  COLUMN_DEFAULT 默认值,  COLUMN_COMMENT 备注   
FROM  INFORMATION_SCHEMA.COLUMNS  
where  
table_schema ='YOUR_SCHEMA_NAME'

达梦数据库查询表结构

SELECTt.table_name AS "表名",tc.comments AS "表注释",c.column_name AS "字段名",c.data_type AS "数据类型",c.data_length AS "长度",c.nullable AS "是否为空",c.data_default AS "默认值",cc.comments AS "字段注释"
FROM(SELECT DISTINCT table_name FROM user_tables) tJOINuser_tab_columns c ON t.table_name = c.table_nameLEFT JOINuser_col_comments cc ON c.table_name = cc.table_name AND c.column_name = cc.column_nameLEFT JOINuser_tab_comments tc ON t.table_name = tc.table_name
group by t.table_name,tc.comments,c.column_name,c.data_type,c.data_length,c.nullable,c.data_default,cc.comments

pgsql

SELECTA.attnum AS "序号",C.relname AS "表名",CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",A.attname AS "字段名",A.attnotnull as 是否为空,
--     -IF(A.attnotnull='f','是','否') AS '必填',concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS "数据类型",d.description AS "注释"
FROMpg_class C,pg_attribute A,pg_type T,pg_description d
WHERE C.relname='table_name'
AND A.attnum > 0 AND A.attrelid = C.oidAND A.atttypid = T.oidAND d.objoid = A.attrelidAND d.objsubid = A.attnum
ORDER BYC.relname DESC,A.attnum ASC;

如果少表，看下是不是表没有注释，使用

SELECTA.attnum AS "序号",C.relname AS "表名",CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",A.attname AS "字段名",A.attnotnull as 是否为空,
--     -IF(A.attnotnull='f','是','否') AS '必填',concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS "数据类型"
FROMpg_class C,pg_attribute A,pg_type T
WHERE C.relname in('flyway_schema_history','kafka_send_info','registration_district','send_book_log','send_mobile_msg_log')AND A.attnum > 0AND A.attrelid = C.oidAND A.atttypid = T.oid
ORDER BYC.relname DESC,A.attnum ASC;

sqlserver

SELECT t.name AS TableName,c.name AS ColumnName,ty.name AS DataType,c.max_length AS MaxLength,c.precision AS Precision,c.scale AS Scale,c.is_nullable AS IsNullable,ic.is_identity AS IsIdentityFROM sys.tables tINNER JOIN sys.columns c ON t.object_id = c.object_idLEFT JOIN sys.types ty ON c.system_type_id = ty.system_type_idLEFT JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_idORDER BY t.name, c.column_id;

数据库导出数据量以及占用空间

mysql数据库查询占用空间

SELECTtable_schema,table_name,CONCAT(ROUND(data_length / 1024 / 1024, 2), 'MB') AS data_length_MB,CONCAT(ROUND(index_length / 1024 / 1024, 2), 'MB') AS index_length_MB
FROMinformation_schema.tables
WHEREtable_schema = 'your_database_name'
ORDER BYtable_name;

达梦数据库查询数据量

SELECT B.OWNER, B.TABLE_NAME, TABLE_ROWCOUNT(B.OWNER, B.TABLE_NAME) "TABLE_ROWS"
FROM (SELECT A.OWNER, A.TABLE_NAMEFROM ALL_TABLES AWHERE A.TABLE_NAME NOT LIKE 'CTI%'AND A.TABLE_NAME NOT LIKE 'SREF_CON_TAB%'AND A.TABLE_NAME NOT LIKE 'BM%') B
ORDER BY 3 DESC, 1, 2;

达梦数据库查询占用空间

  SELECT a.tablespace_name "表空间名称",total / (1024 * 1024) "表空间大小(M)",free / (1024 * 1024) "表空间剩余大小(M)",(total - free) / (1024 * 1024 ) "表空间使用大小(M)",total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)",(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) freeFROM dba_free_spaceGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) totalFROM dba_data_filesGROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

sqlserver数据库数据量以及占用空间磁盘空间

SELECTt.NAME AS TableName,p.rows AS RowCounts,SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROMsys.tables t
INNER JOIN     sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOINsys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOINsys.allocation_units a ON p.partition_id = a.container_id
WHEREt.NAME NOT LIKE 'dt%'AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255
GROUP BYt.NAME, p.Rows
ORDER BYTotalSpaceMB DESC;