SQL Server 2005/2008中是否有将列重置为默认值的方法

Question

一些使用我们软件的人没有按照我们的正常步骤进行数据库服务器移动，因为我们的软件在所有数据库中呈现nulls = 0的遗留部分。因此，由于他们忽略了使用我们的协议，我们留下了大量垃圾空数据和大量针对fieldname = 0的查询，这些查询都失败了，因为fieldname is null是true。

那么，如何构建一个存储过程来将SQL Server数据库中的所有null(实际上不应该有null)恢复为默认值呢？

我几乎没有做存储过程的经验，在Oracle数据库之外的存储过程中也绝对没有经验，所以如果你可以用代码示例来回答这个问题，并且如果不可能在多个表上运行过程，请解释代码在做什么，这也很好，在这种情况下，我只会写一个实用程序来完成它(尽管我已经看到这个实用程序需要9年的时间才能运行)

Answer 1

这将列出表中所有可为null的列：

select tab.name, col.name 
from sys.columns col join sys.tables tab on col.object_id = tab.object_id
where tab.type = 'U' and col.is_nullable = 1

您可以使用一个游标在每个列中循环，执行一些动态SQL来更新列定义。但是，如果每个列中都有不同的数据类型，这可能会变得非常糟糕——它们都一样吗？

或者，您可以更新所有SP查询以使用：

ISNULL(fieldname, 0) = 0

为了消除这方面的NULL问题，这将在NULL或-Zero上匹配。

编辑：

好吧，假设你想更新所有的INT、TINYINT、BIGINT和BIT列，这个脚本应该来完成。不过使用风险自负！备份数据库并将其还原为副本，然后在那里进行测试。然后，取消注释WHERE子句中的表筛选器，以便首先在单个表上进行测试。EXEC会被注释，只有在将其测试到屏幕后才会取消注释。

此脚本将执行以下操作：

1. 将每个NULL INT、TINYINT、BIGINT和BIT更新为零(false)
2. 将每个INT、TINYINT、BIGINT和BIT列更改为NOT NULL
3. 将每个INT、TINYINT、BIGINT和BIT列的默认约束添加为零(false)

请注意，如果列上已经有默认约束，则3将失败。如果不需要默认值(即显式地为每个INSERT插入一个值)，请删除它。

DECLARE @table VARCHAR(MAX), @col VARCHAR(MAX), @type VARCHAR(MAX)
DECLARE @q_update VARCHAR(MAX), @q_alter VARCHAR(MAX), @q_default VARCHAR(MAX)
DECLARE c CURSOR FOR
select tab.name, col.name, typ.name
from sys.columns col
join sys.types typ on col.system_type_id = typ.system_type_id
join sys.tables tab on col.object_id = tab.object_id
where tab.type = 'U' and col.is_nullable = 1 and typ.name IN ('int', 'tinyint', 'bigint', 'bit')
--tab.name = 'sometable'
OPEN c
FETCH NEXT FROM c INTO @table, @col, @type
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'UPDATING ' + @table + '.' + @col + ' (' + @type + ')';
SET @q_update = 'UPDATE [' + @table + '] SET [' + @col + '] = 0 WHERE [' + @col + '] IS NULL';
PRINT @q_update;
--EXEC(@q_update);
PRINT '';
SET @q_alter = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @col + '] ' + @type + ' NOT NULL';
PRINT @q_alter;
--EXEC(@q_alter);
PRINT '';
SET @q_default = 'ALTER TABLE [' + @table + '] ADD CONSTRAINT ' + @table + '_' + @col + '_DF DEFAULT 0 FOR [' + @col + ']'
PRINT @q_default;
--EXEC(@q_default);
PRINT '----';
FETCH NEXT FROM c INTO @table, @col, @type
END
CLOSE c
DEALLOCATE c

注意：为了清晰起见，我使用了光标。如果你真的不想使用它们，请随意查看@JamesCurtis链接。

Answer 2

你的标题说将字段设置为null，但你的问题说不应该有null。然而可以使用DEFAULT关键字将字段设置为其默认值：

Update MyTable set MyField = DEFAULT Where <something>

Answer 3

这里有一个代码示例，可以满足您的需要。

大规模免责声明。此代码会将具有默认值的每个可为null的列重置为默认值。你几乎肯定会想限制它的范围。

-- Routine which finds all NULLABLE columns with default values
-- and sets them back to their defaults.
-- DISCLAIMER.  If in any doubt, comment the EXEC out and copy and paste
-- the contents of @sql into a query window, and inspect before running.
-- Soon to hold executable SQL
DECLARE @sql VARCHAR(Max)
-- Use the INFORMATION_SCHEMA to find all qualifying columns, building
-- up a string consisting of a series of simple update statements
--
-- Warning:  My definition of qualifying may differ from yours.
-- Check your WHERE clause. 
--
-- Doity concatenatin' ahoy, Captain!
SELECT
@sql = 
CASE WHEN
@sql IS NULL
THEN
'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = DEFAULT;'  
ELSE 
@sql + 'UPDATE ' + TABLE_NAME + ' SET ' + COLUMN_NAME + ' = DEFAULT;'   
END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMN_DEFAULT IS NOT NULL
AND IS_NULLABLE = 'YES'
--  Add extra conditions for granularity!!!!
-- Execute sql
EXEC (@sql);