如何找到潜在的非空列? [英] How can I find potential not null columns?

问题描述

我正在使用SQL Server数据库，该数据库对约束的要求很低，并且希望应用一些非空约束.有什么方法可以扫描数据库中的所有可为空的列，并选择不包含任何null甚至更好地计算null值数量的列?

I'm working with a SQL Server database which is very light on constraints and want to apply some not null constraints. Is there any way to scan all nullable columns in the database and select which ones do not contain any nulls or even better count the number of null values?

推荐答案

也许带有一些动态SQL

Perhaps with a little dynamic SQL

示例

Declare @SQL varchar(max) = '>>>'

Select @SQL = @SQL 
            + 'Union All Select TableName='''+quotename(Table_Schema)+'.'+quotename(Table_Name)+''''
                             +',ColumnName='''+quotename(Column_Name)+''''
                             +',NullValues=count(*)'
                         +' From '+quotename(Table_Schema)+'.'+quotename(Table_Name)
                         +' Where '+quotename(Column_Name)+' is null '
  From INFORMATION_SCHEMA.COLUMNS
  Where Is_Nullable='YES'

Select @SQL='Select * from (' + replace(@SQL,'>>>Union All ','') + ') A Where NullValues>0'
Exec(@SQL)

返回(例如)

TableName       ColumnName  NullValues
[dbo].[OD-Map]  [Map-Val2]  185
[dbo].[OD-Map]  [Map-Val3]  225
[dbo].[OD-Map]  [Map-Val4]  225

对于所有计数> = 0的表/列

...
Select @SQL=replace(@SQL,'>>>Union All ','') 
Exec(@SQL)

这篇关于如何找到潜在的非空列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！