MS SQL alter bit fields to be not nullable and have default value.

done:
--MS SQL alter bit fields to be not nullable and have default value.
create table test1 (bt bit)

insert into test1
select null
union
select 1
union
select 0
select * from test1
--create default

 ALTER TABLE test1 ADD CONSTRAINT
     DF_BT_Col   DEFAULT 0 FOR bt

--test
insert into test1 (bt) Values (default)

select * from test1


--not null
alter table test1 alter column bt bit not null
--test
insert into test1
select null

 

Well, that would be great if I was starting from scratch.  Thanks for the effort, but it's not what I explained.

All the tables exist already.  All the fields are already there.  I need to iterate through each table (isn't there a sysobjects or something I can run through?) and...
1.  add the constraint of not null
2.  add the default value of 0 (zero)

I hope this helps.  Please let me know if I'm not being clear in my question.

use yourdb
go
select name from sysobjects where xtype='u'

I bumped this to 100 points.  If that's not enough, please tell me how many you'd want to write what I need without us having to back and forth.
I'm not actively working on the problem.  If I had a few hours I would be able to figure out the code.  I assumed that someone here could knock out the 5 or 10 lines of code in a minute and that I'd plug it in, see it run, give some points and thanks, and move on to the next thing.  Does that make sense?  I'm new here, so if complete answers aren't what should expect (or the point value doesn't warrant that), let me know and I'll take a new approach going forward.
Thanks again.

--iterate each table (programatically in SQL)  for each bit field:


--USE yourDB
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @table_name varchar(50), @column_name varchar(50),
 @def varchar(1000),@nl Varchar(1000)


DECLARE bit_cursor CURSOR FOR
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where data_type='bit'

OPEN bit_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM bit_cursor
INTO @table_name, @column_name

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
 set @def=  'ALTER TABLE '+ @table_name + ' ADD CONSTRAINT
     DF_BT_Col_' +@table_name +'_'+ @column_name + '  DEFAULT 0 FOR '+ @column_name
print @def
exec (@def)
--not null
set @nl= 'alter table '+ @table_name + ' alter column '+ @column_name +' bit not null'
print @nl

exec (@nl)
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM bit_cursor
   INTO @table_name, @column_name
END

CLOSE  bit_cursor
DEALLOCATE  bit_cursor
GO

Thank you.  There's still the matter of all of the records that already exist.

Cannot insert the value NULL into column 'Column', table 'Database.Table'; column does not allow nulls. UPDATE fails.

if it is PK - then it must be Not Null

oops
I thought it is about script.

---
yes , data needs to be good for not null

Well, luckily I have no PKs that are bit fields.  This error happens on a bit field when the table already has data.  If you could fix that last piece (integrated into the partial solution above) I'd really appreciate it!

--ok: I added 'if'' bit is null - to avoid the step and post message in window
--drop table test2

--create table test2 (bit1 bit not null , bit2 bit null )
--select * from  test2
--insert into test2
--select 1,null
--USE yourDB
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @table_name varchar(50), @column_name varchar(50),
 @def varchar(1000),@nl Varchar(1000)


DECLARE bit_cursor CURSOR FOR
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where data_type='bit'

OPEN bit_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM bit_cursor
INTO @table_name, @column_name

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
 set @def=  'ALTER TABLE '+ @table_name + ' ADD CONSTRAINT
     DF_BT_Col_' +@table_name +'_'+ @column_name + '  DEFAULT 0 FOR '+ @column_name
--print @def
exec (@def)
--not null
set @nl= 'if (Select count(*) from '+ @table_name + ' where '+ @column_name +'  is null)=0
Begin
   alter table '+ @table_name + ' alter column '+ @column_name + ' bit not null
 End
else select '''+ @table_name + '. '+ @column_name + ' has NULL: can not do it'''

--print @nl

exec (@nl)
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM bit_cursor
   INTO @table_name, @column_name
END

CLOSE  bit_cursor
DEALLOCATE  bit_cursor
GO

It's great, but skipping the alter just because there's data is not what we need todo.  We need to alter all of them...data or no data.  Thank you for what you've provided so far.

yes ->
you will need to run more the 1 time the script in case there are null ->
or if you wish to change it same time: please  tell to what null need to be chage based on your biz logic

The null bit should be 0 for any existing fields.  Thanks again!

If I said I love you that would just be weird, wouldn't it?

Thank you so much!

:) you are very welcome