查詢資料庫表及表欄位

紫心落發表於2024-12-05
資料庫
-- mysql

SELECT t.TABLE_SCHEMA AS '資料庫名',
       t.TABLE_NAME AS '表名',
       t.TABLE_COMMENT AS '表註釋',
       --REPLACE(REPLACE(t.TABLE_COMMENT,'\r\n',' '),'\n',' ') AS '表註釋',
       c.COLUMN_NAME AS '欄位名',
       c.COLUMN_TYPE AS '欄位型別', -- 長度處理（僅適用於某些型別）
 CASE
     WHEN c.DATA_TYPE IN ('varchar',
                          'char',
                          'text',
                          'blob',
                          'binary') THEN COALESCE(CHARACTER_MAXIMUM_LENGTH, 'N/A')
     ELSE 'N/A'
 END AS '長度',
 c.IS_NULLABLE AS '是否為空',
 c.COLUMN_DEFAULT AS '預設值',
 c.COLUMN_COMMENT AS '欄位註釋'
 -- REPLACE(REPLACE(c.COLUMN_COMMENT,'\r\n',' '),'\n',' ') AS '欄位註釋'
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'bmerp_baseconfig' -- 可選，用於過濾特定資料庫
ORDER BY t.TABLE_SCHEMA,
         t.TABLE_NAME,
         c.ORDINAL_POSITION;

　　查出來就是這樣的

相關文章