September 28, 2009 at 11:06 am
hi as of now when i convert from acess to sql, for some reason all the bit columns in access are not converted to sql to allow null, so i have to go into each and every table to do it manually or create an alter table alter column for each and every table and each and every column,most of the columns that have bit as the datatype are named as "IsActive".
So i was wondering if there is way we can convert all the bit columns in a database to allow null with a single alter statement,
I have looked around but cannot find an answer
thanks
September 28, 2009 at 11:39 am
There isn't a single statement for it, but you could write a script that would do it all at once.
Would look something like this:
select
'alter table ' +
(select name
from sys.schemas
where schema_id =
(select schema_id
from sys.tables
where object_id = columns.object_id)) + '.' +
(select name
from sys.tables
where object_id = columns.object_id) +
' alter column ' + name + ' bit null;'
from sys.columns
where system_type_id = 104
and object_id in
(select object_id
from sys.tables)
and is_nullable = 0;
Copy and paste the results from that into a window in Management Studio and run it on your test/dev database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 11:47 am
Thanks that works thank you so much
September 28, 2009 at 11:51 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 11:58 am
hey i do have a question, u have where system_type_id = 104, I am thinking that this ID of 104 specifies bit,what specifies NText, As i think if i have the value for ntext I can use the same query to convert all the ntext feilds in the database ( while upsizing from acess for some reason memo fields are upsized as ntext, no clue why) to nvarchar(max), You think that is possible,
Where do we obtain these values from ??
I was thinking of something like this
Select
'alter table ' +
(select name
from sys.schemas
where schema_id =
(select schema_id
from sys.tables
where object_id = columns.object_id)) + '.' +
(select name
from sys.tables
where object_id = columns.object_id) +
' alter column ' + name + ' nvarchar(max) null;'
from sys.columns
where system_type_id = " value for ntext"
and object_id in
(select object_id
from sys.tables);
September 28, 2009 at 12:02 pm
select * from sys.systypes
Run that. It'll give you the type ID for every system data type.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 12:40 pm
hey so the query shows that it did alter the bit columns to allow nulls but then when i look inside the table itself the bit fields still donot allow nulls, any ideas as to why this is happening
Please check the attachment, I must be doing something wrong
September 28, 2009 at 1:06 pm
Did you actually run the script from the results?
I tested it, and it reset the columns in my table to allow nulls.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 1:38 pm
From the results ?? do you mean that after i run the script i take the reults and run the script again ??
thanks
September 28, 2009 at 2:07 pm
The results of the script I posted should look something like this:
alter table dbo.MyTable alter column MyBitColumn bit null;
alter table dbo.MyTable alter column MySecondBitColumn bit null;
alter table dbo.MySecondTable alter column AnotherBitColumn bit null;
You would then copy-and-paste those results into Management Studio, and run them as a script.
If you're up for a slightly more complex script, this one doesn't require that step, since it runs the commands itself:
declare AlterBits cursor local fast_forward for
select
'alter table ' +
(select name
from sys.schemas
where schema_id =
(select schema_id
from sys.tables
where object_id = columns.object_id)) + '.' +
(select name
from sys.tables
where object_id = columns.object_id) +
' alter column ' + name + ' bit null;'
from sys.columns
where system_type_id = 104
and object_id in
(select object_id
from sys.tables)
and is_nullable = 0;
declare @SQL varchar(max);
open AlterBits;
fetch next from AlterBits
into @SQL;
while @@fetch_status = 0
begin
exec (@SQL);
fetch next from AlterBits
into @SQL;
end;
close AlterBits;
deallocate AlterBits;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 5:04 pm
Thanks all works perfectly thaks again
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply