找到并罗列出SQL Server中具有默认值的所有字段
2008-10-24 10:07:03 来源:WEB开发网问题
上个星期我在对一个供应商开发的数据库按规定进行故障排除的时候,我们需要对他们数据库中50个表的每一个都进行查看,以确保所有期望是默认值的字段都被分配了默认值。你可以想象这是一个多么令人畏惧的工作,而我立即提出了这个问题。有没有一个比在SQL Server管理套件中打开每一个表来查看这个schema的更好方法吗?
专家解答
通过查询任何数据库中的三个系统表,你可以获得每个表的每一个字段的默认值。下面是这个核心查询。它返回分配给当前数据库中每个用户表的默认值。这个查询在SQL 2000和SQL 2005中都是兼容的。
SELECTSO.NAMEAS"TableName",SC.NAMEAS"ColumnName",SM.TEXTAS"DefaultValue"
FROMdbo.sysobjectsSOINNERJOINdbo.syscolumnsSCONSO.id=SC.id
LEFTJOINdbo.syscommentsSMONSC.cdefault=SM.id
WHERESO.xtype='U'
ORDERBYSO.[name],SC.colid
sysobjects 为我们提供了表元数据。在这个例子中,我们只对表名称感兴趣。syscolumns 表存储与每个表的各个字段相关联的元数据。在这个例子中,我们只需要字段名称。最后,默认值元数据由syscomments表提供。
对Northwind数据库运行这个查询生成下面的结果(为了简短,省略了一些记录)。注意,因为LEFT JOIN到syscomments表所以它将返回NULL默认值。
现在我在想这个很好的基本查询版本有什么选择。。。
选择1:搜索特别的默认值
通过编辑WHERE条件语句,我们可以在所有的表中查看特别的默认值。
SELECTSO.NAMEAS"TableName",SC.NAMEAS"ColumnName",SM.TEXTAS"DefaultValue"
FROMdbo.sysobjectsSOINNERJOINdbo.syscolumnsSCONSO.id=SC.id
LEFTJOINdbo.syscommentsSMONSC.cdefault=SM.id
WHERESO.xtype='U'ANDSM.TEXT='(0)'
ORDERBYSO.[name],SC.colid
选择2:只返回具有默认值字段的信息
修改核心查询的WHERE条件语句来忽略syscomments.text表中的NULL值,这个技巧如下所示:
SELECTSO.NAMEAS"TableName",SC.NAMEAS"ColumnName",SM.TEXTAS"DefaultValue"
FROMdbo.sysobjectsSOINNERJOINdbo.syscolumnsSCONSO.id=SC.id
LEFTJOINdbo.syscommentsSMONSC.cdefault=SM.id
WHERESO.xtype='U'ANDSM.TEXTISNOTNULL
ORDERBYSO.[name],SC.colid
但是,将FROM条件从句中的JOIN从一个LEFT JOIN改为一个INNER JOIN会提供优化:
SELECTSO.NAMEAS"TableName",SC.NAMEAS"ColumnName",SM.TEXTAS"DefaultValue"
FROMdbo.sysobjectsSOINNERJOINdbo.syscolumnsSCONSO.id=SC.id
INNERJOINdbo.syscommentsSMONSC.cdefault=SM.id
WHERESO.xtype='U'
ORDERBYSO.[name],SC.colid
其实还有另一个选择,利用SQL 2005中的系统目录视图。前面的查询给我提供了这时所需要的信息,并在SQL 2000和SQL 2005中都可以使用,在SQL2000实例中可以挖掘出与这个默认值(实际上是一个默认约束)关联的额外元数据。通过将这个查询特定在系统目录视图上,我们可以获得在之前的查询中没有显示出来的额外信息。
SELECTST.[name]AS"TableName",SC.[name]AS"ColumnName",SD.definitionAS"DefaultValue",SD.[name]AS"ConstraintName"
FROMsys.tablesSTINNERJOINsys.syscolumnsSCONST.[object_id]=SC.[id]
INNERJOINsys.default_constraintsSDONST.[object_id]=SD.[parent_object_id]ANDSC.colid=SD.parent_column_id
ORDERBYST.[name],SC.colid
所以记住,就因为你被告知没有更好的方法,依靠你作为一个数据库管理员的本能来钻研。你永远不会知道你可能会得到些什么。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
更多精彩
赞助商链接