找到并罗列出SQL Server中具有默认值的所有字段

本文介绍了一种高效查询SQL Server数据库中所有表及其字段默认值的方法,避免手动检查每个表。通过修改查询条件,可以筛选特定的默认值或仅显示已设置默认值的字段。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题

  上个星期我在对一个供应商开发的数据库按规定进行故障排除的时候,我们需要对他们数据库中50个表的每一个都进行查看,以确保所有期望是默认值的字段都被分配了默认值。你可以想象这是一个多么令人畏惧的工作,而我立即提出了这个问题。有没有一个比在SQL Server管理套件中打开每一个表来查看这个schema的更好方法吗?

  专家解答

  通过查询任何数据库中的三个系统表,你可以获得每个表的每一个字段的默认值。下面是这个核心查询。它返回分配给当前数据库中每个用户表的默认值。这个查询在SQL 2000和SQL 2005中都是兼容的。


  

Quote:
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 




  sysobjects 为我们提供了表元数据。在这个例子中,我们只对表名称感兴趣。syscolumns 表存储与每个表的各个字段相关联的元数据。在这个例子中,我们只需要字段名称。最后,默认值元数据由syscomments表提供。

  对Northwind数据库运行这个查询生成下面的结果(为了简短,省略了一些记录)。注意,因为LEFT JOIN到syscomments表所以它将返回NULL默认值。



现在我在想这个很好的基本查询版本有什么选择。。。

  选择1:搜索特别的默认值

  通过编辑WHERE条件语句,我们可以在所有的表中查看特别的默认值。


  

Quote:
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' AND SM.TEXT = '(0)' 
  ORDER BY SO.[name], SC.colid 



选择2:只返回具有默认值字段的信息

  修改核心查询的WHERE条件语句来忽略syscomments.text表中的NULL值,这个技巧如下所示:


  

Quote:
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' AND SM.TEXT IS NOT NULL 
  ORDER BY SO.[name], SC.colid

 


  但是,将FROM条件从句中的JOIN从一个LEFT JOIN改为一个INNER JOIN会提供优化:


  

Quote:
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 
  INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id 
  WHERE SO.xtype = 'U' 
  ORDER BY SO.[name], SC.colid 






其实还有另一个选择,利用SQL 2005中的系统目录视图。前面的查询给我提供了这时所需要的信息,并在SQL 2000和SQL 2005中都可以使用,在SQL2000实例中可以挖掘出与这个默认值(实际上是一个默认约束)关联的额外元数据。通过将这个查询特定在系统目录视图上,我们可以获得在之前的查询中没有显示出来的额外信息。


  

Quote:
SELECT ST.[name] AS "Table Name", SC.[name] AS "Column Name", SD.definition AS "Default Value", SD.[name] AS "Constraint Name" 
  FROM sys.tables ST INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] 
  INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id 
  ORDER BY ST.[name], SC.colid 






所以记住,就因为你被告知没有更好的方法,依靠你作为一个数据库管理员的本能来钻研。你永远不会知道你可能会得到些什么。

### 如何使用Navicat导数据库 通过Navicat工具可以方便地完成数据库结构或数据的导操作。以下是关于如何利用Navicat实现这一功能的具体方法: #### 使用图形界面方式导数据库结构 可以通过简单的鼠标操作来快速导数据库结构而不包含实际的数据内容。具体的操作流程如下描述的内容所示[^1]。 1. **选择目标数据库** 在Navicat左侧的对象浏览器中找到需要导数据库,右键单击它以打开上下文菜单。 2. **进入高级选项** 在弹的菜单列里选择“转储SQL文件”,随后会现一个对话框,在此点击“高级”按钮以便进一步自定义导参数。 3. **调整导设置** 进入到高级设置窗口之后,取消选中标记为“插入语句”的复选框,从而确保只导结构而忽略任何数据条目。 4. **指定保存位置** 将目标设定为“文件”,同时输入或者浏览选取合适的存储路径以及命名即将生成的SQL脚本文件的位置和名称。 5. **执行导过程** 完成上述所有配置步骤后启动导进程直至结束提示成功消息即示已完成整个任务。 #### SQL查询法提取特定数据库内的格架构详情 除了GUI交互手段之外还可以借助标准SQL命令行形式获取更加精确定制化的结果集。下面提供了一个示例性的查询语句用于展示某个给定模式下各张及其属性的信息摘要[^3]: ```sql SELECT COLUMN_NAME AS 名称, COLUMN_TYPE AS 数据类型, IF(IS_NULLABLE='NO', '是', '否') AS 是否允许为空, COLUMN_DEFAULT AS 默认值, COLUMN_COMMENT AS 注释 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name'; ``` 请注意以上代码片段里的`your_database_name`与`your_table_name`两处占位符需替换为你所关心的实际对象名字才能正常运行返回预期的结果集合。 另外还有一种稍微复杂一点但是能够一次性罗列来当前选定范围内全部关联信息的方式可供参考选用[^5]: ```sql SELECT TABLE_NAME AS 名, COLUMN_NAME AS 字段, COLUMN_COMMENT AS 字段名称, COLUMN_TYPE AS 数据类型, IS_NULLABLE AS 是否为空, COLUMN_KEY AS 约束条件, COLUMN_COMMENT AS 备注 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'databasename' ORDER BY table_name; ``` 同样记得把这里的`databasename`替换成真实的源端标识字符串才行哦! ### 总结 无论是采用直观易懂的可视化操控还是灵活强大的编程接口调用途径都能够满足不同层次用户对于从Navicat环境中迁移去既有资源的需求。希望这些指导材料可以帮助您顺利完成相应的工作事项。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值