How to change all bit type column NULL values to false in all database in MSSQL?

Question

I have a database in with I have many bit type columns. After adding other columns I need all old columns to have default "false" values.

Answer 1

To update each old column to 0, you can use this query (on a column-by-column basis):

UPDATE MyTable
SET OldCol1 = 0
WHERE OldCol1 IS NULL

Then, if you want any future values to have a default of 0 as well, use this:

ALTER TABLE MyTable
ALTER COLUMN OldCol1 bit NOT NULL

ALTER TABLE MyTable
ADD CONSTRAINT OldCol1ShouldBeFalse DEFAULT 0 FOR OldCol1

Now, you'll have to run this against each old column, so hopefully you don't have too many.

Answer 2

This nested dynamic SQL will achieve the job if you don't want to do this one by one. Note that it doesn't do any checking first to validate that the column doesn't already have a default constraint (which is possible, even if the column is nullable), but the script could be augmented to do so (and even to drop such a constraint in case it is default 1).

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DECLARE @sql NVARCHAR(MAX); SET @sql = N'''';';

SELECT @sql = @sql + N'
  SELECT @sql = @sql + N''UPDATE ' + QUOTENAME(name) 
  + '.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '' 
  SET '' + QUOTENAME(c.name) + '' = 0 
  WHERE '' + QUOTENAME(c.name) + '' IS NULL;

  ALTER TABLE ' + QUOTENAME(name) + '.'' + QUOTENAME(s.name) 
    + ''.'' + QUOTENAME(t.name) 
    + '' ADD CONSTRAINT '' + c.name + ''_FalseByDefault 
    DEFAULT (0) FOR '' + QUOTENAME(c.name) + '';

  ALTER TABLE ' + QUOTENAME(name) + '.'' + QUOTENAME(s.name) 
    + ''.'' + QUOTENAME(t.name) 
    + '' ALTER COLUMN '' + QUOTENAME(c.name) + '' BIT NOT NULL;
  ''

FROM ' + QUOTENAME(name) + '.sys.columns AS c
INNER JOIN ' + QUOTENAME(name) + '.sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id = 104 AND c.is_nullable = 1
AND t.is_ms_shipped = 0;'
FROM sys.databases WHERE database_id > 4;

SET @sql = @sql + 'PRINT @sql;
  --EXEC sp_executesql @sql;';

EXEC sp_executesql @sql;

It's really ugly to look at, and the PRINT command will not necessarily be complete due to output limitations in Management Studio. But once you are confident that the first few commands look right, you can comment that out and uncomment the second last line and run the script again to make the changes. The GO is there because some may fail. You may want to add print output for each db/table/column combination so that you can print status messages to more easily correlate any potential errors with the actual db/table/column.