# SQL 全域搜尋語法
###### tags: `軟體語法筆記`

## 一、SQL SERVER

### 查某 table 的欄位資料(可自行調整)

# 請先 USE 到要查詢的 schema 下

SELECT
    a.TABLE_NAME                as 表格名稱,
    b.COLUMN_NAME               as 欄位名稱,
    b.DATA_TYPE                 as 資料型別,
    b.CHARACTER_MAXIMUM_LENGTH  as 最大長度,
    b.COLUMN_DEFAULT            as 預設值,
    b.IS_NULLABLE               as 允許空值,
    (
        SELECT
            value
        FROM
            fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 
                                     a.TABLE_NAME, 'column', default)
        WHERE
            name='MS_Description' 
            and objtype='COLUMN' 
            and objname Collate Chinese_Taiwan_Stroke_CI_AS=b.COLUMN_NAME
    ) as 欄位備註
FROM
    INFORMATION_SCHEMA.TABLES  a
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON (a.TABLE_NAME=b.TABLE_NAME)
WHERE
    TABLE_TYPE='BASE TABLE'
AND
    a.TABLE_NAME = 'UCAR_CNTRT_DTL '
    
ORDER BY
    a.TABLE_NAME, ordinal_position

## 二、ORACLE

### 查所有資料表的內容(可自行調整)

SELECT A.OWNER AS TABLE_OWNER
      ,A.TABLE_NAME
      ,REPLACE(C.COMMENTS,CHR(10),' ') AS TABLE_COMMENTS
      ,A.COLUMN_NAME
      ,A.DATA_TYPE
      ,A.DATA_LENGTH
      ,A.DATA_SCALE
      ,A.COLUMN_ID AS COLUMN_POSITION
      ,REPLACE(B.COMMENTS,CHR(10),' ') AS COLUMN_COMMENTS
      ,MAX(D.INDEX_NAME) AS INDX_NME_MAX
      ,MAX(F.CONSTRAINT_NAME) AS PK_NME_MAX
FROM ALL_TAB_COLUMNS A
LEFT JOIN ALL_COL_COMMENTS B ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN ALL_TAB_COMMENTS C ON A.OWNER = C.OWNER AND A.TABLE_NAME = C.TABLE_NAME
LEFT JOIN ALL_IND_COLUMNS D ON A.OWNER = D.TABLE_OWNER AND A.TABLE_NAME = D.TABLE_NAME AND A.COLUMN_NAME = D.COLUMN_NAME
LEFT JOIN ALL_CONS_COLUMNS E ON A.OWNER = E.OWNER AND A.TABLE_NAME = E.TABLE_NAME AND A.COLUMN_NAME = E.COLUMN_NAME
LEFT JOIN ALL_CONSTRAINTS F ON E.OWNER = F.OWNER AND E.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND F.CONSTRAINT_TYPE = 'P' 
WHERE ( A.OWNER = 'FAS' AND A.TABLE_NAME IN ( 'CDC_GUAR_FUND' ) ) /* 請替換為需要的Table Name */
GROUP BY A.OWNER
        ,A.TABLE_NAME
        ,REPLACE(C.COMMENTS,CHR(10),' ')
        ,A.COLUMN_NAME
        ,A.DATA_TYPE
        ,A.DATA_LENGTH
        ,A.DATA_SCALE
        ,A.COLUMN_ID
        ,REPLACE(B.COMMENTS,CHR(10),' ')
ORDER BY A.OWNER
       , A.TABLE_NAME
       , A.COLUMN_ID
;