AlenWu的程式學習筆記

撰寫程式時，常需要將參考Table Schema文件以了解DB中Table的欄位內容，我自己的習慣是先寫好Schema文件，才建立Table

但有時候接手別人的專案時，並不一定有完整的文件，所以就想直接從DB中取得資訊產生文件的念頭，用以下的SQL語法可快速的取得DB中所有Table的欄位清單

SELECT st.name [Table_Name], sc.column_id [Column_Sort], sc.name [Column], ic.DATA_TYPE +
    CASE 
	WHEN ISNULL(ic.CHARACTER_MAXIMUM_LENGTH, '') = '' THEN ''
    ELSE '(' + CAST(ic.CHARACTER_MAXIMUM_LENGTH AS varchar) + ')'
    END [Data_Type], 
	ISNULL(ic.COLUMN_DEFAULT, '') [Default_Value],
    CASE sc.is_identity 
	WHEN 1 THEN 'Y'
    ELSE ''
    END [Identity],
    CASE
    WHEN ISNULL(ik.TABLE_NAME, '') <> '' THEN 'Y'
    ELSE ''
    END [PK], 
	ISNULL(sep.value, '') [Description], ISNULL(p.value, '') [Table_Description]
FROM sys.tables st
INNER JOIN sys.columns sc
    ON st.object_id = sc.object_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS ic
    ON ic.TABLE_NAME = st.name
        AND ic.COLUMN_NAME = sc.name
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ik
    ON ik.TABLE_NAME = ic.TABLE_NAME
        AND ik.COLUMN_NAME = ic.COLUMN_NAME
LEFT JOIN sys.extended_properties sep
    ON st.object_id = sep.major_id
        AND sc.column_id = sep.minor_id
        AND sep.name = 'MS_Description'
LEFT JOIN sys.extended_properties p
    ON p.major_id = st.object_id
        AND p.minor_id = 0
        AND p.name = 'MS_Description'
ORDER BY  st.name, sc.column_id

以北風資料庫為例，產生的資料如下：

其中Description需要在Column中自行輸入說明，輸入的位置如下圖圖：

Table_Description則是Table的說明，可在Table屬性的擴充屬性中輸入名稱="MS_Description"，值=Table的說明文字，如下圖：

抓出來的資料，配上[NPOI]以ASP .NET配上NPOI 2.2.1產生xlsx檔，就可以輕鬆產出Table Schema文件。