這是我之前研究出來的一段 SQL 語法,可以用來查詢資料庫的所有表格欄位定義(俗稱資料字典),可以查出資料庫中所有表格的 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'
ORDER BY
a.TABLE_NAME, ordinal_position
以下是另一個版本:
SELECT tab.name table_name,
col.colid column_id,
col.name column_name,
typ.name data_type,
col.prec PRECISION,
col.scale scale,
col.length,
com.TEXT default_value,
CASE
WHEN col.isnullable = 1 THEN 'Y'
ELSE 'N'
END is_nullable,
CASE
WHEN col.status & 0X80 = 0X80 THEN 'Y'
ELSE 'N'
END is_identity,
(SELECT VALUE
FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table',
tab.name,
'column',
col.name)) DESCRIPTION
FROM sysobjects tab,
syscolumns col
LEFT OUTER JOIN syscomments com
INNER JOIN sysobjects obj
ON com.id = obj.id
ON col.cdefault = com.id
AND com.colid = 1,
systypes typ
WHERE tab.id = col.id
AND tab.xtype = 'U'
AND col.xusertype = typ.xusertype
ORDER BY
tab.name, col.colid