一露戒、怎么用Sql語句獲取一個(gè)數(shù)據(jù)庫中的所有表的名字及描述
SELECT a.name, b.value
FROM sys.all_objects a
LEFT JOIN sys.extended_properties b ON a.object_id = b.major_id
WHERE a.type = 'U'
AND b.minor_id = 0
ORDER BY a.name
// sys.all_objects中保bai存了該庫du中所有對(duì)zhi象的信息蜻韭，daosys.extended_properties中保存了該庫中雖有專對(duì)象的擴(kuò)展屬性屬信息。
// a.type='U' 篩選出表
// b.minor_id = 0 篩選出僅是表的屬性，而不是字段的屬性
二画侣、 導(dǎo)出數(shù)據(jù)字典
SELECT 表名 = CASE
WHEN a.colorder = 1 THEN d.name
ELSE ''
END, 表說明 = CASE
WHEN a.colorder = 1 THEN isnull(f.value, '')
ELSE ''
END
, 字段序號(hào) = a.colorder, 字段名 = a.name
, 標(biāo)識(shí) = CASE
WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END, 主鍵 = CASE
WHEN EXISTS (
SELECT 1
FROM sysobjects
WHERE xtype = 'PK'
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
, 類型 = b.name, 占用字節(jié)數(shù) = a.length
, 長(zhǎng)度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION')
, 小數(shù)位數(shù) = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0)
, 允許空 = CASE
WHEN a.isnullable = 1 THEN '√'
ELSE ''
END
, 默認(rèn)值 = isnull(e.text, '')
, 字段說明 = isnull(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = 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
ORDER BY a.id, a.colorder
結(jié)束！
