数据归集

MySQL

表结构导出

在服务器执行sql语句

mysql -u your_user -p -e "SELECT 
    ROW_NUMBER() OVER (ORDER BY c.table_name, c.ordinal_position) AS 序号,
    t.table_name AS 表名,
    t.table_comment AS 表备注,
    c.column_name AS 字段名,
    c.column_type AS 字段类型,
    c.column_comment AS 字段备注,
    IF(c.column_key = 'PRI', '是', '否') AS 是否主键,
    IF(c.is_nullable = 'NO', '是', '否') AS 是否必填
FROM 
    information_schema.COLUMNS c
JOIN 
    information_schema.TABLES t ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE 
    c.table_schema = 'your_database'  -- 替换为你的数据库名
ORDER BY 
    c.table_name, c.ordinal_position;" --batch --raw > /data/backup/output.csv

下载到windows后使用文本文档打开，并且转换为 ANSI 编码，然后再使用excel中的 数据 ->  从文本/CSV 将数据加载到excel中。

 

SQLServer

表结构导出

表信息查询

select TABLE_CATALOG,ds.value description ,TABLE_NAME
 from INFORMATION_SCHEMA.TABLES t
 left join sysobjects tbs on tbs.name=t.TABLE_NAME
 left join sys.extended_properties ds on ds.major_id=tbs.id and ds.minor_id=0
 where TABLE_TYPE='BASE TABLE' 
 and TABLE_CATALOG='数据库库名' 
 and TABLE_SCHEMA not in ('sys','INFORMATION_SCHEMA')

 

查询表字段信息

SELECT 表名 = d.name,
       表备注 = ISNULL(f.value, ''),
       字段名 = a.name,
       字段类型 = b.name+'('+ cast(COLUMNPROPERTY(a.id, a.name, 'PRECISION') as varchar) + ')',
       字段备注 = ISNULL(g.[value], ''),
       是否主键 = CASE
                WHEN EXISTS
                     (
                         SELECT 1
                         FROM sysobjects
                         WHERE xtype = 'PK'
                               AND parent_obj = a.id
                               AND name IN
                                   (
                                       SELECT name
                                       FROM sysindexes
                                       WHERE indid IN
                                             (
                                                 SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
                                             )
                                   )
                     ) THEN
                    '是'
                ELSE
                    '否'
            END,
       允许必填 = CASE
                 WHEN a.isnullable = 1 THEN
                     '否'
                 ELSE
                     '是'
             END
FROM syscolumns a
    LEFT JOIN systypes b
        ON a.xusertype = 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.major_id
           AND f.minor_id = 0
-- WHERE d.name in ('table_name)  -- 如只需部分表则通过条件来过滤
ORDER BY a.id, a.colorder;

 

达梦

表结构导出

SELECT
    ROW_NUMBER() OVER (ORDER BY a.table_name, a.column_name) AS 序号, -- 序号，使用窗口函数生成行号
    a.table_name AS 表名, -- 表名
    c.comments AS 表备注, -- 表备注
    a.column_name AS 字段名, -- 字段名
    a.data_type || DECODE(a.data_precision, NULL, '', '(' || a.data_precision || DECODE(a.data_scale, NULL, '', ',' || a.data_scale) || ')') AS 字段类型, -- 字段类型，包含精度和小数位数
    d.comments AS 字段备注, -- 字段备注
    CASE WHEN e.constraint_name IS NOT NULL THEN '是' ELSE '否' END AS 是否主键, -- 判断是否为主键
    CASE WHEN a.nullable = 'N' THEN '是' ELSE '否' END AS 是否必填 -- 判断是否必填
FROM
    user_tab_columns a -- 用户表的列信息
LEFT JOIN user_tab_comments c ON a.table_name = c.table_name -- 用户表的注释信息
LEFT JOIN user_col_comments d ON a.table_name = d.table_name AND a.column_name = d.column_name -- 用户表列的注释信息
LEFT JOIN (
    SELECT
        cols.table_name,
        cols.column_name,
        cons.constraint_name
    FROM
        user_cons_columns cols
    LEFT JOIN user_constraints cons ON cols.constraint_name = cons.constraint_name
    WHERE
        cons.constraint_type = 'P' -- 主键约束
) e ON a.table_name = e.table_name AND a.column_name = e.column_name -- 主键约束信息
ORDER BY
    a.table_name, -- 按表名排序
    a.column_name; -- 按字段名排序

 

查询数据库数据量大小

SELECT F.TABLESPACE_NAME,
       (T.TOTAL_SPACE - F.FREE_SPACE) / 1024 "USED (GB)",
       F.FREE_SPACE / 1024 "FREE (GB)",
       T.TOTAL_SPACE / 1024  "TOTAL(GB)",
       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BLOCKS *
                         (SELECT PARA_VALUE / 1024
                            FROM V$DM_INI
                           WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;