SQL查询以创建MSSQL数据库的数据字典视图

为了帮助数据库管理员和开发人员更好地理解数据库结构，下面提供了一段SQL脚本，用于创建一个名为`vw_db_dictionary`的视图。此视图将显示数据库中所有用户定义表的相关信息，包括但不限于表名、表描述、字段名称、字段描述、字段类型、字段长度、字段精度、字段是否允许空值、字段默认值、字段是否为自增字段以及字段是否为主键。

```sql
CREATE VIEW dbo.vw_db_dictionary
AS
SELECT
o.name AS table_name,
p.[value] AS table_desc,
c.name AS field,
cp.[value] AS field_desc,
t.name AS field_type,
c.length AS field_size,
ISNULL(COLUMNPROPERTY(c.id, c.name, 'Scale'), 0) AS field_precision,
c.isnullable AS nullable,
ISNULL(COLUMNPROPERTY(c.id, c.name, 'IsIdentity'), 0) AS is_identity,
CASE WHEN EXISTS (
SELECT 1
FROM sysobjects pk
JOIN sysindexes i ON pk.name = i.name
JOIN sysindexkeys ik ON i.indid = ik.indid
WHERE ik.id = c.id AND ik.colid = c.colid AND pk.xtype = 'PK'
) THEN 1 ELSE 0 END AS is_key,
ISNULL(cm.text, '') AS default_value
FROM
syscolumns c
JOIN
sysobjects o ON c.id = o.id
JOIN
systypes t ON c.xtype = t.xtype
LEFT JOIN
sysproperties p ON o.id = p.id AND p.smallid = 0
LEFT JOIN
sysproperties cp ON c.id = cp.id AND c.colid = cp.smallid
LEFT JOIN
syscomments cm ON c.cdefault = cm.id
WHERE
o.xtype = 'U'
ORDER BY
o.name, c.colid;
```

通过执行上述SQL脚本，您可以轻松地生成一个全面的数据字典，这对于数据库设计的审查、文档编制和日常管理都非常有用。