SQL Server:在数据库中搜索所有值为NULL的列

Question

我有一个数据库，里面有大约420个表。所有表都有Audit列：field1、field2、field3。

如何编写查询来检查整个数据库中这些审核列中是否存在NULL值？

Answer 1

下面应该会为您提供一个技巧，如果您需要不同的输出，可以随意使用。

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256) = ''
DECLARE @ColumnName nvarchar(100)
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
-- check tables
SET @TableName = 
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
-- check if above table has columns we want to check
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)      
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND COLUMN_NAME IN ('field1', 'field2', 'field3')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
-- insert into temp table if column value is null
IF @ColumnName IS NOT NULL
INSERT INTO #Results EXEC('SELECT DISTINCT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +' WHERE ' + @ColumnName + ' IS NULL ')
END   
END
-- return results
SELECT ColumnName, ColumnValue FROM #Results
-- drop temp table
DROP TABLE #Results