Update All Null of Field (data Tape "Bit") To False

Question

Post reply

Update All Null of Field (data Tape "Bit") To False

babak3334000

SSChasing Mays

Points: 637
More actions
January 16, 2012 at 10:57 pm

#253613

Update All Null of Field (data Type "Bit") To False By T-Sql

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

UPDATETableName

SETColumnName = 0

WHEREColumnName IS NULL

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

babak3334000 SSChasing Mays Points: 637 More actions · Answer 2

For All Table in the Data Base

I have many Table , No one Table

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I would do those updates manually (imho). You need to document what columns of which tables need to change, produce a script for each change that needs to be implemented, and then run those scripts - after a FULL backup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

In that case you can use the INFORMATION_SCHEMA.COLUMNS view, and create a Dynamic Script that will update all columns with bit data type

It will be better if you follow Jason's suggestions as this seems to be a big and responsible task

Try it and get back if you are stuck somewhere

Edit: Didn't see Jason's reply before posting

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Kingston Dhasian (1/16/2012)
In that case you can use the INFORMATION_SCHEMA.COLUMNS view, and create a Dynamic Script that will update all columns with bit data type
Try it and get back if you are stuck somewhere

That will work. Here's a hint and a part of the reason I wouldn't do it (besides what I already mentioned) - you will need at least one cursor to get this working properly.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

babak3334000 SSChasing Mays Points: 637 More actions · Answer 6

For every Column Data Type Is "Bit"

Example :

select * from syscolumns where xtype = 104 --: bit

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

babak3334000 (1/16/2012)
For every Column Data Type Is "Bit"
Example :
select * from syscolumns where xtype = 104 --: bit

Understood.

While you are changing the values, I would seriously consider revising your database design if every column in the database is a bit field.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

SQLRNNR (1/16/2012)
Kingston Dhasian (1/16/2012)
In that case you can use the INFORMATION_SCHEMA.COLUMNS view, and create a Dynamic Script that will update all columns with bit data type
Try it and get back if you are stuck somewhere
That will work. Here's a hint and a part of the reason I wouldn't do it (besides what I already mentioned) - you will need at least one cursor to get this working properly.

I completely agree with you, Jason

This is a task which has to be done responsibly and with proper documentation

But, I don't think a CURSOR is mandatory to script out the UPDATE's

You can do it even without a CURSOR

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

True if you are just scripting out the updates. I think the OP wants something that will auto-execute those updates.

These kinds of wide sweeping data changes lend themselves to too much risk. Do it extremely cautiously and take your time doing it. Get the business to sign off on all of the changes and just do it bit by bit.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 10

Kingston Dhasian (1/16/2012)
In that case you can use the INFORMATION_SCHEMA.COLUMNS view, and create a Dynamic Script that will update all columns with bit data type...

Just FYI: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 11

SQLRNNR (1/16/2012)
babak3334000 (1/16/2012)
For every Column Data Type Is "Bit"
Example :
select * from syscolumns where xtype = 104 --: bit
...I would seriously consider revising your database design if every column in the database is a bit field.

He means 'for every column that has the bit data type', not 'every column has the bit data type' 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

Kingston Dhasian (1/16/2012)
But, I don't think a CURSOR is mandatory to script out the UPDATE's
You can do it even without a CURSOR

It's not, but unless you have a well-tested set-based script already, writing it using a cursor is likely to be quicker and less error-prone. Not all cursors are evil!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mhike2hale SSC Eights! Points: 858 More actions · Answer 13

Can you try this.

declare @queries varchar(max)

set @queries = ''

select @queries = @queries + 'update ' + b.name + ' set ' + a.name + ' = 0 where ' + a.name + ' is null' + char(10)

from sys.columns a

inner join sys.tables b on a.object_id = b.object_id

inner join sys.types c on a.system_type_id = c.system_type_id

where b.name != 'sysdiagrams'

and c.name = 'bit'

order by b.name, a.name

print @queries--to verify the queries to be executed

--exec(@queries)--execute your query

"Often speak with code not with word,
A simple solution for a simple question"

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 14

SQL Kiwi (1/16/2012)
Kingston Dhasian (1/16/2012)
But, I don't think a CURSOR is mandatory to script out the UPDATE's
You can do it even without a CURSOR
It's not, but unless you have a well-tested set-based script already, writing it using a cursor is likely to be quicker and less error-prone. Not all cursors are evil!

I have done this sort of things before and hence, the confidence that I can do it again 🙂

But as Jason said, its better to do this responsibly and hence I won't give the code to the OP directly

Also, thanks for the link, Paul

I had previously heard from somewhere (I don't remember it exactly from where) that we should always INFORMATION_SCHEMA views as its a standard and the structure of these views are not subject to change. Views like sys.columns, sys.indexes, etc are subject to change.

But now, it seems I will have to change my thinking again.:-)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/