上个星期我在对一个供应商开发的数据库按规定进行故障排除的时候，我们需要对他们数据库中50个表的每一个都进行查看，以确保所有期望是默认值的字段都被分配了默认值。你可以想象这是一个多么令人畏惧的工作，而我立即提出了这个问题。有没有一个比在SQL Server管理套件中打开每一个表来查看这个schema的更好方法吗？
在SQL Server中，管理和查询数据库结构是数据库管理员和开发人员日常任务的重要部分。当你需要检查所有字段是否设置了默认值时，手动检查每个表显然效率低下。幸运的是，SQL Server提供了系统表和目录视图，可以帮助你快速获取所需信息。本文将详细介绍如何利用SQL Server的系统表和目录视图来列出具有默认值的所有字段。
我们来看一下核心查询，这个查询是从三个系统表——`sysobjects`、`syscolumns`和`syscomments`——中获取默认值信息的。`sysobjects`存储了数据库对象的信息，`syscolumns`包含了关于表中每列的详细数据，而`syscomments`则用于保存对象的注释和默认值的文本。
```sql
SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO
INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
ORDER BY SO.[name], SC.colid
```
这个查询返回当前数据库中所有用户表（`xtype = 'U'`表示用户表）的字段名及其默认值。`LEFT JOIN`用于包含那些没有默认值的字段，它们的`SM.TEXT`值将为NULL。
如果你想要查找特定的默认值，可以修改`WHERE`子句，例如查找默认值为`(0)`的字段：
```sql
WHERE SO.xtype = 'U' AND SM.TEXT = '(0)'
```
若只想显示有默认值的字段，可以调整`WHERE`子句，去除`LEFT JOIN`的NULL值，或者直接使用`INNER JOIN`：
```sql
WHERE SO.xtype = 'U' AND SM.TEXT IS NOT NULL
```
或者
```sql
INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id
```
在SQL Server 2005及更高版本中，推荐使用系统目录视图，如`sys.columns`和`sys.default_constraints`，它们提供了更直接的方式来获取这些信息：
```sql
SELECT T.name AS "Table Name", C.name AS "Column Name", DC.definition AS "Default Value"
FROM sys.tables T
INNER JOIN sys.columns C ON T.object_id = C.object_id
LEFT JOIN sys.default_constraints DC ON C.object_id = DC.parent_object_id AND C.column_id = DC.parent_column_id
WHERE T.type_desc = 'USER_TABLE'
ORDER BY T.name, C.column_id
```
这个查询利用了`sys.tables`（表示表）、`sys.columns`（表示列）和`sys.default_constraints`（表示默认约束）目录视图，能更直观地获取默认值信息，并且适用于SQL Server 2005及更高版本。
通过以上查询，你可以有效地管理和检查SQL Server数据库中所有表的默认值设置，大大提高了工作效率。在进行数据库维护、审计或问题排查时，这种方法尤其有用。了解并熟练使用这些系统表和目录视图，对于提升SQL Server数据库管理的效率至关重要。
