Building Data Dictionary using SQL Server 2005

When design a  new system, you can using sql statements blew to build the data dictionary document ,

it's very fast ,the statements can help you  a lot,you just need to copy the result to your excel document.

--build index dictionary

SELECT O.name AS '表名',
I.name AS '索引名称',
(CASE I.type_desc WHEN  'CLUSTERED' THEN '聚集索引' WHEN 'NONCLUSTERED' THEN '非聚聚索引' ELSE '' END ) AS '索引类型',
(CASE SUBSTRING(I.NAME,1, 3) WHEN  'PK_' THEN '主键聚集索引    ' ELSE '' END) AS '描述'
FROM sys.indexes AS I
INNER JOIN sys.objects AS O ON I.object_id = O.object_id
WHERE O.type = 'U'
AND O.name NOT LIKE '%Z_%'
ORDER BY O.name ASC , I.name DESC

--build table and column dictionary

SELECT O.name '表名',
C.name '列名',
(case C.is_nullable WHEN 1 THEN 'yes' ELSE 'no' END ) AS '是否允许为空',
T.name AS '数据类型',
T.max_length AS '数据长度',
D.definition AS '默认值'
FROM sys.objects AS O
INNER JOIN sys.columns AS C ON C.object_id = O.object_id
LEFT JOIN sys.default_constraints AS D ON D.object_id = C.default_object_id AND D.type = 'D'
INNER JOIN sys.types AS T ON C.system_type_id = T.system_type_id AND T.schema_id = 4
WHERE O.type = 'U'