Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3792
  • Last Modified:

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

This solution needs to work for SQL 2000 and 2005.  

I walked into a DB that has many nullable bit fields that have no default value.  I want to iterate each table (programatically in SQL) and for each bit field:
1.  add the constraint of not null
2.  add the default value of 0 (zero)

I don't know the system objects well enough to do this myself.  The only gotcha that I can see is how to handle tables that are already populated.  If you are an expert and see a flaw in what I'm trying to do, please let me know.

Thank you!
0
sjd01032
Asked:
sjd01032
  • 9
  • 7
1 Solution
 
Eugene ZCommented:
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

 
0
 
sjd01032Author Commented:
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.
0
 
Eugene ZCommented:
use yourdb
go
select name from sysobjects where xtype='u'
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sjd01032Author Commented:
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.
0
 
Eugene ZCommented:


--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

0
 
sjd01032Author Commented:
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.
0
 
Eugene ZCommented:
if it is PK - then it must be Not Null
0
 
Eugene ZCommented:
oops
I thought it is about script.

---
yes , data needs to be good for not null
0
 
sjd01032Author Commented:
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!
0
 
Eugene ZCommented:
--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
0
 
sjd01032Author Commented:
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.
0
 
Eugene ZCommented:
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
0
 
sjd01032Author Commented:
The null bit should be 0 for any existing fields.  Thanks again!
0
 
Eugene ZCommented:
--try it:

--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)

set @nl= 'Startagain: 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  
Begin
update  '+ @table_name + ' set '+ @column_name + '=0 where '+ @column_name + ' is  null
 goto Startagain
End'

--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
0
 
sjd01032Author Commented:
If I said I love you that would just be weird, wouldn't it?

Thank you so much!
0
 
Eugene ZCommented:
:) you are very welcome
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now